Rails & Postgres selecting sub keys of a JSONB column

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):

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']?

B-M · Accepted Answer · 2022-01-27 03:24:28Z

0

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.

answered Jan 27, 2022 at 3:24

B-M

1,2881 gold badge21 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Rails & Postgres selecting sub keys of a JSONB column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related