Select all values for given key from JSON array column

user_3015734 0
Viewed 102

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.

postgresql
1 Answers

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}
user_30157340
Related