CODEWITHSUNDEEP
arrayspostgresqlaggregate-functionsjsonbpostgresql-10
axay
axay

Reputation: 1707

How to extract elements of a JSONB array?

Running PostgresSQL v10.5.

In my table table_a that has a column metadata which is of type jsonb.

It has a JSON array as one of it's keys array_key with value something like this:

[{"key1":"value11", "key2":"value21", "key3":"value31"}, 
 {"key1":"value21", "key2":"value22", "key3":"value23"}]

This is how I can query this key

SELECT metadata->>'array_key' from table_a

This gives me the entire array. Is there any way I can query only selected keys and perhaps format them?

The type of the array is text i.e pg_typeof(metadata->>'array_key') is text

An ideal output would be

"value11, value13", "value21, value23"

Upvotes: 5

Views: 7273

Answers (1)

klin
klin

Reputation: 121804

Use jsonb_array_elements() to get elements of the array (as value) which can be filtered by keys:

select value->>'key1' as key1, value->>'key3' as key3
from table_a
cross join jsonb_array_elements(metadata->'array_key');

  key1   |  key3   
---------+---------
 value11 | value31
 value21 | value23
(2 rows)

Use an aggregate to get the output as a single value for each row, e.g.:

select string_agg(concat_ws(', ', value->>'key1', value->>'key3'), '; ')
from table_a
cross join jsonb_array_elements(metadata->'array_key')
group by id;

             string_agg             
------------------------------------
 value11, value31; value21, value23
(1 row)

Working example in rextester.

Upvotes: 5

Related Questions