Re: How to select values in a JSON type of column?

Snjezana Frketic schrieb am 18.11.2020 um 17:00:
> I actually have version 9.3.17 😬
>
>
> On Wed, 18 Nov 2020 at 15:55, Thomas Kellerer <shammat@gmx.net <mailto:shammat@gmx.net>> wrote:
>
>     Snjezana Frketic schrieb am 18.11.2020 um 11:29:
>      > I have a column called |targeting| in a table called |campaigns| .
>      > [...]
>      > and I need to select all the |ids| in |includes|.
>      > Currently, I am doing it like this
>      >
>      > SELECT |targeting#>'{targets,0,audienceSegments,0,includes,0,segments,allOf,0,ids}'FROM campaigns;|
>      >
>
>     If you are on Postgres 12 or later, this can be done using jsonb_path_query_array:
>
>        select jsonb_path_query_array(targeting, '$.targets[*].*.includes.**.ids.id <http://ids.id>')
>        from campaigns

If you are limited to an unsupported version, you need to go down the hierarchy manually:

select t.ids
from campaigns c
    cross join lateral (
      select array_agg(s2.seg2 ->> 'id') as ids
      from json_array_elements(c.targeting -> 'targets') as t(target)
        cross join json_array_elements(t.target -> 'audienceSegments') as a(aud)
        cross join json_array_elements(a.aud -> 'includes') as i(include)
        cross join json_array_elements(i.include #> '{segments,allOf}') as s(seg)
        cross join json_array_elements(s.seg -> 'ids') as s2(seg2)
    ) t