From: | Snjezana Frketic <frketic(dot)snjezana(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | How to select values in a JSON type of column? |
Date: | 2020-11-18 10:29:44 |
Message-ID: | CA+9_ahaS5YkqQ5B8=S6KRgSaspk0uedT5gBP0NS_YU2ym9O68g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Phil Florent | 2020-11-18 10:46:47 | No parallel plan on an union all subquery |
Previous Message | Marcin Giedz | 2020-11-18 10:05:47 | Re: pg_upgrade from 12 to 13 failes with plpython2 |