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.g.johns...@gmail.com> wrote:
> On Mon, Apr 6, 2020 at 7:21 PM AC Gomez <ant...@gmail.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.
>
>
