Postgres parse array/json

Question

I have the following rows in the table src where one of the attributes is an array of JSONs.

Attribute:

[{"key": "Tag", "value": "myTagValue"}, {"key": "Brand", "value": "myBrandValue"}]
[{"key": "Tag", "value": "myTagValue"}, {"key": "Brand", "value": "myBrandOtherValue"}, {"key": "Test", "value": "123"}]

How does one select the Brand?

Expected output:

Brand
-----
myBrandValue
myBrandOtherValue

(2 rows affected)

I was thinking of using json_to_recordset(json) function, but the attribute can have N different values (jsons).

Is this an array of JSON json[] or is this a JSON array?

S-Man
– S-Man

2021-02-22 16:52:35 +00:00
Commented Feb 22, 2021 at 16:52 — S-Man
– S-Man, Commented Feb 22, 2021 at 16:52

S-Man · Accepted Answer · 2021-02-22 16:56:47Z

1

demo:db<>fiddle

SELECT
    elems.value ->> 'value'              -- 3
FROM mytable,
    jsonb_array_elements(mydata) elems   -- 1 
WHERE elems.value ->> 'key' = 'Brand'    -- 2

Extract all array elements into one row per JSON element
Filter the JSON elements by key = Brand
Return value

answered Feb 22, 2021 at 16:56

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

Collectives™ on Stack Overflow

Postgres parse array/json

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related