4

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

1 Answer 1

5

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;
2
  • Which version of Postgres are you on? When I run this I get the error set-returning functions are not allowed in CASE
    – Andy
    Apr 1, 2020 at 1:10
  • I think it was Postgres 9.2
    – vibby
    Apr 2, 2020 at 6:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.