From my current query, I obtain this jsonb data :
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');