Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


Online example: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=ee7f6e73055ffb3a98fcfd3d86763e35

Thomas






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux