Concatenate list from JSON array

Question

Let's say I have json in the database of items:

Row1:

{"Id": "1", "Items": [{"Item": "Test Item", "Price": "$5.00"}, {"Item": "Test Item #2", "Price": "$15.00"}]}

Row2:

{"Id": "2", "Items": [{"Item": "Test Item #3", "Price": "$1.00"}, {"Item": "Test Item #1", "Price": "$4.00"}]}

How do I get rows formatted like this (| is column seperator):

1 | Test Item, Test Item #2
2 | Test Item #3, Test Item #1

Christian · Accepted Answer · 2015-12-03 19:04:25Z

2

SELECT  ID || '|' || ARRAY_TO_STRING( ARRAY_AGG( ITEMS ), ', ')
FROM
    (
        SELECT T.J->>'Id' AS ID, json_array_elements((T.J->'Items')::json)->>'Item' AS ITEMS
        FROM
        (
            SELECT ('{"Id": "1", "Items": [{"Item": "Test Item", "Price": "$5.00"}, {"Item": "Test Item #2", "Price": "$15.00"}]}')::json AS J
            UNION all
            SELECT ('{"Id": "2", "Items": [{"Item": "Test Item #3", "Price": "$1.00"}, {"Item": "Test Item #1", "Price": "$4.00"}]}')::json AS J
        ) T
    ) T
GROUP   BY ID

answered Dec 3, 2015 at 19:04

Christian

7,3509 gold badges58 silver badges81 bronze badges

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

1 Comment

Blue Over a year ago

Wasn't looking for a literal | but this leads me to what I need.

Collectives™ on Stack Overflow

Concatenate list from JSON array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related