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]
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments