jsonpostgresqlfieldanonymous

Avoid anonymous fields in JSON built from table rows

I have a table:

CREATE TABLE test (
  item_id INTEGER NOT NULL,
  item_name VARCHAR(255) NOT NULL,
  mal_item_name VARCHAR(255),
  active CHAR(1) NOT NULL,
  data_needed CHAR(1) NOT NULL,
  parent_id INTEGER);

The query:

select array_to_json(array_agg(row_to_json(t))) 
from (select item_id as id, 
             item_name as text, 
             parent_id as parent,
             (mal_item_name,data_needed) as data 
      from test) t

produces result:

[{"id":1,"text":"Materials","parent":0, "data": {"f1":null,"f2":"N"}},
 {"id":2,"text":"Bricks","parent":1, "data":{"f1":null,"f2":"N"}},
 {"id":3,"text":"Class(high)","parent":2, "data":{"f1":null,"f2":"Y"}},
 {"id":4,"text":"Class(low)","parent":2, "data":{"f1":null,"f2":"Y"}}]

The original field names mal_item_name and data_needed are replaced with f1 and f2.
How can I get a JSON with field names itself? Documentation says by creating a type for these two fields. Is there an alternative?

Solution

  • Use json_build_object() in Postgres 9.4 or later:

    SELECT json_agg(t) AS js
FROM  (SELECT item_id   AS id
            , item_name AS text
            , parent_id AS parent
            , json_build_object('mal_item_name', mal_item_name
                               ,'data_needed', data_needed) AS data
       FROM test) t;

    And use json_agg(...) instead of array_to_json(array_agg(row_to_json(...))).

    For Postgres 9.3:

    SELECT json_agg(t) AS js
FROM  (SELECT item_id   AS id
            , item_name AS text
            , parent_id AS parent
            , (SELECT t FROM (SELECT mal_item_name, data_needed) 
                                AS t(mal_item_name, data_needed)) AS data
       FROM test) t;

    Details: