From: | Snjezana Frketic <frketic(dot)snjezana(at)gmail(dot)com> |
---|---|
To: | Thomas Kellerer <shammat(at)gmx(dot)net> |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: How to select values in a JSON type of column? |
Date: | 2020-11-18 16:21:57 |
Message-ID: | CA+9_ahY37NCoLbSFFPjstnxJSHS-gWJsSZ01YJQmaJOPVarj7Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Unfortunately, I also can not update my version :)
On Wed, 18 Nov 2020 at 17:00, Snjezana Frketic <frketic(dot)snjezana(at)gmail(dot)com>
wrote:
> I actually have version 9.3.17 😬
>
>
> On Wed, 18 Nov 2020 at 15:55, Thomas Kellerer <shammat(at)gmx(dot)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
>>
>>
>> Online example:
>> https://dbfiddle.uk/?rdbms=postgres_12&fiddle=ee7f6e73055ffb3a98fcfd3d86763e35
>>
>> Thomas
>>
>>
>>
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2020-11-18 16:22:52 | Re: psql backward compatibility |
Previous Message | Adrian Klaver | 2020-11-18 16:16:43 | Re: psql backward compatibility |