Avoid anonymous fields in JSON built from table rows [duplicate]

Question

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?

Your version of Postgres would be essential to the question. — Erwin Brandstetter
– Erwin Brandstetter, Commented Jan 21, 2015 at 13:34

Erwin Brandstetter · Accepted Answer · 2022-05-05 02:08:12Z

0

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:

edited May 5, 2022 at 2:08

answered Jan 21, 2015 at 13:43

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Thara Over a year ago

I am using Postgres 9.3

Erwin Brandstetter Over a year ago

Added a solution for 9.3. Be sure to follow the links for detailed explanation.

Thara Over a year ago

Thanks a lot. This is what I looked for. Will go through details link for sure.

Collectives™ on Stack Overflow

Avoid anonymous fields in JSON built from table rows [duplicate]

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related