Select query in row_to_json function

Question

For example , I use the following function to convert rows into json in PostgreSQL 9.2

select row_to_json(row(productid, product)) from gtab04;

and this will returns below results

row_to_json
---------------
{"f1":3029,"f2":"DIBIZIDE M TAB"}
{"f1":3026,"f2":"MELMET 1000 SR TAB"}
{"f1":2715,"f2":"GLUCORED FORTE"}
{"f1":3377,"f2":"AZINDICA 500 TAB"}

• unfortunately it loses the field names and replaces them with f1, f2, f3, etc.
• How can I get the actual field names or cast field name?

Answer 1

To work around this we must either create a row type and cast the row to that type or use a subquery. A subquery will typically be easier.

select row_to_json(t)
from (
   select productid, product from gtab04
) t

Answer 2

If one wants to prevent a sub-query, json_build_object() might be a solution. It does not map the column name, but let's your set the JSON keys explicitly.

---

Query

SELECT json_build_object('productid', productid, 'product', product) FROM gtab04;

json_build_object                                
------------------
{"productid":3029,"product":"DIBIZIDE M TAB"}
{"productid":3026,"product":"MELMET 1000 SR TAB"}
{"productid":2715,"product":"GLUCORED FORTE"}  
{"productid":3377,"product":"AZINDICA 500 TAB"}

---

View on DB Fiddle

Answer 3

Just remove the row() from your query.

select row_to_json(productid, product) from gtab04;

This will make sure row column names are maintained.