How to select values in a JSON type of column?

2020-11-18 Thread Snjezana Frketic
Hi!

I have a column called targeting in a table called campaigns .
The column looks like

{
   "targets": [
 {
   "audienceSegments": [
 {
   "includes": [
 {
   "consumer": "selection",
   "segments": {
 "allOf": [
   {
 "provider": "a",
 "ids": [
   {
 "id": "110418"
   },
   {
 "id": "110430"
   },
   {
 "id": "110433"
   }
 ]
   }
 ]
   }
 }
   ],
   "excludes": [
 {
   "consumer": "selection",
   "segments": {
 "allOf": [
   {
 "provider": "a",
 "ids": [
   {
 "id": "109776"
   }
 ]
   }
 ]
   }
 }
   ]
 }
   ]
 }
   ]
 }

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;

and that works, but, I don’t want to have a fixed path because positions
could change like 0 could become 1, includes and excludes could change
positions, allOf  could be anyOf etc.
Any idea of how to always select ids in includes no matter the changes?

Thank you!
Anna


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

2020-11-18 Thread Snjezana Frketic
I looked at it yesterday, but I couldn't figure it out because my JSON is
more nested and I got lost going down the path.


On Wed, 18 Nov 2020 at 15:40, David G. Johnston 
wrote:

>
> On Wednesday, November 18, 2020, Snjezana Frketic <
> frketic.snjez...@gmail.com> wrote:
>
>>
>>
>> SELECT
>> targeting#>'{targets,0,audienceSegments,0,includes,0,segments,allOf,0,ids}'FROM
>> campaigns;
>>
>> and that works, but, I don’t want to have a fixed path because positions
>> could change like 0 could become 1, includes and excludes could change
>> positions, allOf  could be anyOf etc.
>> Any idea of how to always select ids in includes no matter the changes?
>>
>
>   Maybe it can be done using json path:
>
>
> https://www.postgresql.org/docs/13/functions-json.html#FUNCTIONS-SQLJSON-PATH
>
> David J.
>
>


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

2020-11-18 Thread Snjezana Frketic
I actually have version 9.3.17 😬


On Wed, 18 Nov 2020 at 15:55, Thomas Kellerer  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
>
>
> Online example:
> https://dbfiddle.uk/?rdbms=postgres_12&fiddle=ee7f6e73055ffb3a98fcfd3d86763e35
>
> Thomas
>
>
>


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

2020-11-18 Thread Snjezana Frketic
Unfortunately, I also can not update my version :)

On Wed, 18 Nov 2020 at 17:00, Snjezana Frketic 
wrote:

> I actually have version 9.3.17 😬
>
>
> On Wed, 18 Nov 2020 at 15:55, Thomas Kellerer  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
>>
>>
>> Online example:
>> https://dbfiddle.uk/?rdbms=postgres_12&fiddle=ee7f6e73055ffb3a98fcfd3d86763e35
>>
>> Thomas
>>
>>
>>


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

2020-11-18 Thread Snjezana Frketic
Fair point.
Appreciate your help nevertheless :)


On Wed, 18 Nov 2020 at 17:30, David G. Johnston 
wrote:

> On Wed, Nov 18, 2020 at 9:23 AM Snjezana Frketic <
> frketic.snjez...@gmail.com> wrote:
>
>> Unfortunately, I also can not update my version :)
>>
>
> Then probably the answer to your original question is no :)
>
> There are possibly other ways to make something that works but if you
> aren't willing to upgrade off of a discontinued version, onto one which has
> a perfectly usable solution, then my interest in pondering a work-around is
> near zero.
>
> David J.
>
>