How to select values in a JSON type of column?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

How to select values in a JSON type of column?

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 1includes 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
Reply | Threaded
Open this post in threaded view
|

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

David G Johnston

On Wednesday, November 18, 2020, Snjezana Frketic <[hidden email]> 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 1includes 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:


David J.

Reply | Threaded
Open this post in threaded view
|

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

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 <[hidden email]> wrote:

On Wednesday, November 18, 2020, Snjezana Frketic <[hidden email]> 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 1includes 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:


David J.

Reply | Threaded
Open this post in threaded view
|

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

Thomas Kellerer-4
In reply to this post by Snjezana Frketic
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


Reply | Threaded
Open this post in threaded view
|

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

Snjezana Frketic
I actually have version 9.3.17 😬


On Wed, 18 Nov 2020 at 15:55, Thomas Kellerer <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

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

Snjezana Frketic
Unfortunately, I also can not update my version :) 

On Wed, 18 Nov 2020 at 17:00, Snjezana Frketic <[hidden email]> wrote:
I actually have version 9.3.17 😬


On Wed, 18 Nov 2020 at 15:55, Thomas Kellerer <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

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

David G Johnston
On Wed, Nov 18, 2020 at 9:23 AM Snjezana Frketic <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

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

Snjezana Frketic
Fair point. 
Appreciate your help nevertheless :) 


On Wed, 18 Nov 2020 at 17:30, David G. Johnston <[hidden email]> wrote:
On Wed, Nov 18, 2020 at 9:23 AM Snjezana Frketic <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

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

Thomas Kellerer-4
In reply to this post by Snjezana Frketic
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 <[hidden email] <mailto:[hidden email]>> 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