How can I map jsonb value into a new array in PostgreSQL?

Question

I'm fairly new to PostgreSQL specially to the whole set of functions it has to manage JSONs. I have this json example where I can grab always one element but, I want to map it to a new array:

select '{"a": {"b":{"c": [{"id":"foo"},{"id":"fuu"}]}}}'::json#>'{a,b,c}';

          column
-----------------------------
 [{"id":"foo"},{"id":"fuu"}]
(1 row)

What I really want is:

 [foo, fuu]

user330315 · Accepted Answer · 2021-08-26 20:22:53Z

10

If you are using Postgres 12 or newer you can use a JSON path query:

select jsonb_path_query_array(the_column, '$.a.b.c[*].id')
from the_table

This assumes that the column is a jsonb column (which it should be). If it's not you need to cast it: the_column::jsonb

answered Aug 26, 2021 at 20:22

user330315

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

Collectives™ on Stack Overflow

How can I map jsonb value into a new array in PostgreSQL?

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