On Wed, 18 Nov 2020 at 15:55, Thomas Kellerer <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') from campaigns