Postgres 12 JSONB key selection with array value

Question

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?

• user1822: Where is board_member in your (invalid) sample JSON?
• B-M: @a_horse_with_no_name, I've added an edit to the sample json.

Answer 1

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