sqljsonpostgresqlpostgresql-9.2composite-types

Select query in row_to_json function


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"}

Solution

  • 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