postgresql - SQL requests on array of array (postgres) - Stack Overflow

I am quite struggling to make a SQL request to fit my need. I have like the following data:

id attribute
1  {"a":["b",["b","c"],["e","f","g"]]}
2  {"b":true}

Thus attribute is a json field with a non unique schema. What I am trying to have as an output is the following:

id attribute
1  ["b","b.c","e.f.g"]
2  true

So far I have some complex SQL query (in my opinion) that barrely do the job:

WITH temp AS (
SELECT 
    id,
    string_to_array(attribute -> 'a',',') as bar,
         array_agg(   array(
                SELECT string_agg(val, '.') 
                FROM jsonb_array_elements_text(item) AS val
            )) as foo
FROM test
   LEFT JOIN jsonb_array_elements(attribute -> 'b') AS item ON TRUE
GROUP BY 1,2
)
SELECT id, replace(coalesce(bar,NULLIF(foo, '{{NULL}}')),'true','all')
FROM temp;

Thank you for help.

I am quite struggling to make a SQL request to fit my need. I have like the following data:

id attribute
1  {"a":["b",["b","c"],["e","f","g"]]}
2  {"b":true}

Thus attribute is a json field with a non unique schema. What I am trying to have as an output is the following:

id attribute
1  ["b","b.c","e.f.g"]
2  true

So far I have some complex SQL query (in my opinion) that barrely do the job:

WITH temp AS (
SELECT 
    id,
    string_to_array(attribute -> 'a',',') as bar,
         array_agg(   array(
                SELECT string_agg(val, '.') 
                FROM jsonb_array_elements_text(item) AS val
            )) as foo
FROM test
   LEFT JOIN jsonb_array_elements(attribute -> 'b') AS item ON TRUE
GROUP BY 1,2
)
SELECT id, replace(coalesce(bar,NULLIF(foo, '{{NULL}}')),'true','all')
FROM temp;

Thank you for help.

• sql
• postgresql

Share Improve this question Follow edited Feb 13 at 7:21 Guillaume Outters 2,73711 gold badge1818 silver badges2121 bronze badges asked Feb 11 at 12:40 PdeuxaPdeuxa 69988 silver badges2929 bronze badges 3

• what is your question? – jhole Commented Feb 11 at 14:00
• I find that the way I did is terrible to understand and yet complex. My question is: what is the best way of doing it in term of maintainability and best pratices? – Pdeuxa Commented Feb 11 at 14:04
• What would you do with a row that has both a and b properties? – Charlieface Commented Feb 11 at 14:59

Add a comment  | 

1 Answer 1

Sorted by: Reset to default -1

If you need just "the first property of the top-level object", then:

select
  id,
  attribute->>((array(select jsonb_object_keys(attribute)))[1]) attribute
from test;

If you have a more complex handling (choose one path for objects having a, else another one), add some cases.

(from PostgreSQL 14, see in a fiddle)

(greatly inspired from another SO question from 2016)