CODEWITHSUNDEEP
arraysjsonpostgresql
DNac
DNac

Reputation: 2783

Postgres parse array/json

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).

Upvotes: 0

Views: 62

Answers (1)

S-Man
S-Man

Reputation: 23676

demo:db<>fiddle

SELECT
    elems.value ->> 'value'              -- 3
FROM mytable,
    jsonb_array_elements(mydata) elems   -- 1 
WHERE elems.value ->> 'key' = 'Brand'    -- 2
  1. Extract all array elements into one row per JSON element
  2. Filter the JSON elements by key = Brand
  3. Return value

Upvotes: 1

Related Questions