Concatenate string values of objects in a postgresql json array

2
votes

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

2
arraysjsonpostgresql
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
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
Hint: string_agg postgresql.org/docs/9.5/static/functions-aggregate.html – jcaron
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

2 Answers

4
votes

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
0
votes

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;