patternsqlMinor
PostgreSql : flatten json array data
Viewed 0 times
postgresqlarrayjsondataflatten
Problem
From my current query, I obtain this jsonb data :
And I want to flatten it on one level only, like this :
Here is a simplfied way to get data in a query (only 2 levels are possible, never more):
I tried to use jsonb_array_elements but my problem is that : I don’t know if it is a json array or not ! Not expert in SQL, I did not find a way to code something like :
For a «zoom out» view of my current data, here is a table-free working test :
For after, I will use the result in an insert statement :
values: "a1", ["b1", "b2"]And I want to flatten it on one level only, like this :
values: "a1", "b1", "b2"Here is a simplfied way to get data in a query (only 2 levels are possible, never more):
SELECT *
FROM jsonb_array_elements('{"test": ["a1", ["b1", "b2"]]}'::jsonb->'test');I tried to use jsonb_array_elements but my problem is that : I don’t know if it is a json array or not ! Not expert in SQL, I did not find a way to code something like :
SELECT
IF (is_json_array(list))
jsonb_array_elements(list)
ELSE
list
ENDIF
FROM jsonb_array_elements('{"test": ["a1", ["b1", "b2"]]}'::jsonb->'test');For a «zoom out» view of my current data, here is a table-free working test :
with recursive search_key_recursive (jsonlevel) as(
values ('{"fr": {"WantedKey": "a1", "Sub": [{"WantedKey": ["b1", "b2"]}], "AnotherSub": [{"WantedKey": "c1"}]}}'::jsonb)
union all
select
case jsonb_typeof(jsonlevel)
when 'object' then (jsonb_each(jsonlevel)).value
when 'array' then jsonb_array_elements(jsonlevel)
end as jsonlevel
from search_key_recursive where jsonb_typeof(jsonlevel) in ('object', 'array')
)
select search_key_recursive.jsonlevel->'WantedKey'
from search_key_recursive
where jsonlevel ? 'WantedKey';For after, I will use the result in an insert statement :
INSERT INTO table1
SELECT 'someText', value
FROM jsonb_array_elements('{"test": ["a1", "b1", "c1"]}'::jsonb->'test');Solution
Got it !
SELECT
case jsonb_typeof(json)
when 'string' then json->>0
when 'array' then jsonb_array_elements_text(json)
end
FROM jsonb_array_elements('{"test": ["a1", ["b1", "b2"]]}'::jsonb->'test') as json;Code Snippets
SELECT
case jsonb_typeof(json)
when 'string' then json->>0
when 'array' then jsonb_array_elements_text(json)
end
FROM jsonb_array_elements('{"test": ["a1", ["b1", "b2"]]}'::jsonb->'test') as json;Context
StackExchange Database Administrators Q#125912, answer score: 5
Revisions (0)
No revisions yet.