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
):
key: [ {first_name: 'Hugo', last_name: 'Grant', ind: true }, {first_name: 'Larry', last_name: 'Larson', ind: false }, {first_name: 'Rick', last_name: 'Flair', ind: 'true' } ]
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 (& how do I add the bit for the ind = [true, 'true']
?
Advertisement
Answer
select id, array_to_string( ( select array_agg(concat_ws(' ', p.item ->> 'first_name', p.item ->> 'last_name')) from jsonb_array_elements(jsonb_path_query_array(d.data, '$.board_members[*] ? (@.bank_employee == true || @.bank_employee == "true")')) as p(item) ), ', ' ) as names from my_table d
I achieved what I wanted with the above. Thought I’d throw it on here.