From: | AC Gomez <antklc(at)gmail(dot)com> |
---|---|
To: | |
Cc: | "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: extract property value from set of json arrays |
Date: | 2020-04-07 03:43:18 |
Message-ID: | CABtmK-h36JZWttFeujFG54+YMa9ryvLKnKYBMQ7a9BwkTdSz+A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
figured it out:
select unnest(array_agg(e.db ->> 'e')) as j
from tbl_t t
cross join lateral jsonb_array_elements((t.jdata->>'b')::jsonb)
as c(e)
On Mon, Apr 6, 2020 at 10:51 PM David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> On Mon, Apr 6, 2020 at 7:21 PM AC Gomez <antklc(at)gmail(dot)com> wrote:
>
>> I have the following in a postgresql table
>> row 1: {"a": 1, "b": "[{"c": "123", "d":"456", "e": "789"}, {"c": "222",
>> "d":"111", "e": "000"} ]"}
>> row 2: {"a": 2, "b": "[{"c": "XXX", "d":"YYY", "e": "ZZZ"}, {"c": "666",
>> "d":"444", "e": "333"} ]"}
>>
>> How do I pullout all "b":"e" values and end up with this result:
>> 789
>> 000
>> ZZZ
>> 333
>>
>
> Two approaches:
>
> 1. Wait for someone else to figure it out and give you the answer.
>
> If 1. takes too long:
>
> 2. Read up on json operators.
>
> https://www.postgresql.org/docs/12/functions-json.html
>
> Then work out something that either works or gets you at least close. If
> you are just close post the work done to date and any relevant points of
> confusion.
>
> I'll then likely be willing and able to fill in the missing gap(s) and
> provide a relevant explanation.
>
> You should formulate your query so that it doesn't require CREATE TABLE.
> WITH vals (v) AS (VALUES (''::json)) SELECT vals.v FROM vals; makes
> experimenting very easy.
>
> Also, indicate which version of PostgreSQL you are working with.
>
> David J.
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | AC Gomez | 2020-04-07 04:08:15 | what happens when you issue ALTER SERVER in a hot environment? |
Previous Message | David G. Johnston | 2020-04-07 02:51:33 | Re: extract property value from set of json arrays |