0

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?

2
  • Where is board_member in your (invalid) sample JSON? Jun 29 at 6:12
  • @a_horse_with_no_name, I've added an edit to the sample json.
    – B-M
    Jun 29 at 13:19
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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