CODEWITHSUNDEEP
sqljsonpostgresql
Angela Kay
Angela Kay

Reputation: 45

Extract key value pairs from array of objects

I have a table called "apples" that looks like this:

| not_a_pk | data (jsonb)                                                                                                                 |
|----------|-----------------------------------------------------------------------------------------------------------------------|
| 1        | [{"key":"color", "value":"red", "notes":"good apple"}, {"key":"size", "value":"large", "notes":"pretty large apple"}] |
| 2        | [{"key":"color", "value":"green", "notes":"too green"}, {"key":"size", "value":"small", "notes":"could be bigger"}]   |
| 2        | [{"key":"color", "value":"purple", "notes":"weird"}, {"key":"size", "value":"miniscule", "notes":"actually a grape"}] |

I'd like to extract part of the object with the keys "key" and "value", as well as group by not_a_pk so that it looks like this:

| not_a_pk | data                                                                                                                                        |
|----------|---------------------------------------------------------------------------------------------------------------------------------------------|
| 1        | [{"key":"color", "value":"red"}, {"key":"size", "value":"large"}]                                                                           |
| 2        | [{"key":"color", "value":"green"}, {"key":"size", "value":"small"}, {"key":"color", "value":"purple"}, {"key":"size", "value":"miniscule"}] |

I've been able to just grab all the values under a single key, like {"key": ["color", "size", "color", "size"]} but I can't figure out how to keep them as regular objects with separate keys.

Upvotes: 0

Views: 458

Answers (1)

user330315
user330315

Reputation:

You need to unnest the array elements, construct the new JSON objects and then aggregate back into an array:

select a.not_a_pk, 
       jsonb_agg(jsonb_build_object('key', x.item -> 'key', 'value', x.item -> 'value')) as data
from apples a
  cross join jsonb_array_elements(a.data) as x(item)
group by a.not_a_pk
order by a.not_a_pk;

Online example

Upvotes: 3

Related Questions