Select all values for given key from JSON array column

Question

I have a json array column, foo:

[{"a": "my_key_type", "b": "foo"}]
[{"a": "my_other_key_type", "b": "foo"}]
[]
[{"a": "my_key_type", "b": "bar"}, {"a": "my_other_key_type", "b": "baz"}]

I would like to select into an array all a values for each JSON element in the column. For instance:

['my_key_type']
['my_other_key_type']
[]
['my_key_type','my_other_key_type']

I was initially pursuing json_object_keys, but that doesn't seem to accomplish what I'm actually after, and it doesn't work on an array type. I have not found a function in the documentation that seems to accomplish what I'm after.

TayTay · Accepted Answer · 2021-12-09 13:03:21Z

2

I was able to solve this with a bit of workaround by exploding the JSON elements into rows, and then using array_agg:

    with keys as (
        select
            id,
            json_array_elements(my_column)->>'a' as e
        from my_table
    )
    select
        id,
        array_agg(e) as a
    from keys
    group by id;

This gives:

id | a
----------------
1  | {my_key_type}
2  | {my_other_key_type}
4  | {my_key_type,my_other_key_type}

answered Dec 9, 2021 at 13:03

TayTay

7,2385 gold badges48 silver badges69 bronze badges

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

Comments

Nik · Accepted Answer · 2023-03-09 21:26:02Z

1

A clearer and more concise solution can be:

SELECT string_agg(value->>'a', ',') AS a_list
FROM (
  SELECT jsonb_array_elements(foo::jsonb) AS value from your_table 
) s;

edited Mar 9, 2023 at 21:26

answered Mar 9, 2023 at 21:05

Nik

3472 silver badges11 bronze badges

Collectives™ on Stack Overflow

Select all values for given key from JSON array column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related