Concatenate string values of objects in a postgresql json array

Question

I have a postgresql table with a json column filled with objects nested in array. Now I want to build a query that returns the id and concatenated string values of objects in the json column. PostgreSQL version is 9.5.

Example data

CREATE TABLE test
(
    id integer,
    data json
);

INSERT INTO test (id, data) VALUES (1, '{
    "info":"a",
    "items":[
        { "name":"a_1" },
        { "name":"a_2" },
        { "name":"a_3" }
    ]
}');

INSERT INTO test (id, data) VALUES (2, '{
    "info":"b",
    "items":[
        { "name":"b_1" },
        { "name":"b_2" },
        { "name":"b_3" }
    ]
}');

INSERT INTO test (id, data) VALUES (3, '{
    "info":"c",
    "items":[
        { "name":"c_1" },
        { "name":"c_2" },
        { "name":"c_3" }
    ]
}');

Not quite working as intended example

So far I've been able to get the values from the table, unfortunately without the strings being added to one another.

SELECT
  row.id,
  item ->> 'name'
FROM
  test as row,
  json_array_elements(row.data #> '{items}' ) as item;

Which will output:

id | names
----------
 1 | a_1
 1 | a_2
 1 | a_3
 2 | b_1
 2 | b_2
 2 | b_3
 3 | c_1
 3 | c_2
 3 | c_3

Intended output example

How would a query look like that returns this output?

id | names
----------
 1 | a_1, a_2, a_3
 2 | b_1, b_2, b_3
 3 | c_1, c_2, c_3

SQL Fiddle link

Note that unless you need to capture the exact details of JSON, like key ordering and how it's spaced, the JSONB type is preferred. JSON stores an exact text copy of the JSON, JSONB stores it an a more efficient binary format. postgresql.org/docs/9.5/static/datatype-json.html — Schwern
– Schwern, Commented Nov 11, 2017 at 23:17
Thanks for the tip, the real data is jsonb, the examples are json to keep it simple. As far as I know json and jsonb column types behave identical. — Simono
– Simono, Commented Nov 11, 2017 at 23:28
Hint: string_agg postgresql.org/docs/9.5/static/functions-aggregate.html — jcaron
– jcaron, Commented Nov 11, 2017 at 23:40
I do believe string_agg is part of the solution to the problem, but I'm at a complete loss on how to do it. — Simono
– Simono, Commented Nov 12, 2017 at 0:32

Haleemur Ali · Accepted Answer · 2017-11-13 02:45:37Z

7

+150

Your original attempt was missing a group by step

This should work:

SELECT
    id
  , STRING_AGG(item->>'name', ', ')
FROM
  test,
  json_array_elements(test.data->'items') as item
GROUP BY 1

answered Nov 13, 2017 at 2:45

Haleemur Ali

28.6k6 gold badges67 silver badges89 bronze badges

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

Comments

carrot · Accepted Answer · 2017-11-12 20:41:32Z

0

By changing the SQL for the second column into an array should give the required results ....

SELECT
  row.id,  ARRAY (SELECT item ->> 'name'
FROM
  test as row1,
  json_array_elements(row.data #> '{items}' ) as item WHERE row.id=row1.id)

FROM
  test as row;

answered Nov 12, 2017 at 20:41

carrot

961 bronze badge

1 Comment

Simono Over a year ago

Thank you for your response, unfortunately I do need the second column to be one string, not an array.

Collectives™ on Stack Overflow

Concatenate string values of objects in a postgresql json array

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related