PostgreSql : flatten json array data

Question

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');

Answer 1

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;

• Andy: Which version of Postgres are you on? When I run this I get the error set-returning functions are not allowed in CASE
• vibby: I think it was Postgres 9.2