PostgreSQL 12 – JSONB Key Selection with Array Value

jsonpostgresql

I have a column of a database table that is of type JSONB and I'm wanting to get some data from that column. For the most part the column is a flat list of key \ value pairs.

Ex:

{ s_key: 'value', s_key1: 'value', s_key2: 'value' ...etc }

However, the key I'm after contains an array of JSON data (or can be null/nil). So the table would be something like:

id: <UUID>,
data: {
    s_key: 'value',
    s_key1: 'value',
    board_members: [ 
       {first_name: 'Hugo', last_name: 'Grant', ind: true }, 
       {first_name: 'Larry', last_name: 'Larson', ind: false },
       {first_name: 'Rick', last_name: 'Flair', ind: 'true' } ]
     }
created_at: <Timestamp>

Now, what I want I do is have a sub select that gives me the concat'd name string (first_name + last_name) based on the ind (whether or not it's true/'true'). So, I want an output of:

[ 'Hugo Grant', 'Rick Flair' ]

I've achieved this — to a degree — with this PSQL Snippet:

    select t.id, array_agg(t._name) as _board
    from (
        select 
            d.id,
            jsonb_extract_path_text(jsonb_array_elements(
                case jsonb_extract_path(d.data, 'board_members') 
                    when 'null' then '[{}]'::jsonb 
                    else jsonb_extract_path(d.data, 'board_members') 
                end
            ), 'first_name') || ' ' || jsonb_extract_path_text(jsonb_array_elements(
                case jsonb_extract_path(d.data, 'board_members') 
                    when 'null' then '[{}]'::jsonb 
                    else jsonb_extract_path(d.data, 'board_members') 
                end
            ), 'last_name') as _name
        from my_table d
        group by d.id
    ) t
    group by t.id

Is there a way to simplify the SQL statement?

Best Answer

You can use jsonb_path_query_array to get all matching array elements:

jsonb_path_query_array(data, '$.board_members[*] ? (@.ind == true)')

The above returns

[
  {"ind": true, "last_name": "Grant", "first_name": "Hugo"}, 
  {"ind": true, "last_name": "Flair", "first_name": "Rick"}
]

for your sample data.

To get the concatenated first/lastname you need to unnest the array and aggregate the names back.

select id, 
       (select jsonb_agg(concat_ws(' ', p.item ->> 'first_name', p.item ->> 'last_name'))
        from jsonb_array_elements(jsonb_path_query_array(data, '$.board_members[*] ? (@.ind == true)')) as p(item)) as names
from my_table

The above returns ["Hugo Grant", "Rick Flair"] in the names column

Online example

Related Question