Re: Extracting data from jsonb array?

On Mon, Dec 7, 2020 at 9:54 PM Tom Lane wrote: > Ken Tanzer writes: > > There's one last piece of this query I'm clearly not getting though. > Where > > it says: > > > from foo as f, jsonb_to_recordset(js) as t(key2 text) > > > what is actually going on there? I keep reading this as a table

Re: Extracting data from jsonb array?

Ken Tanzer writes: > There's one last piece of this query I'm clearly not getting though. Where > it says: > from foo as f, jsonb_to_recordset(js) as t(key2 text) > what is actually going on there? I keep reading this as a table foo (f) > cross-joined to a table created by jsonb_to_recordset

Re: Extracting data from jsonb array?

On Monday, December 7, 2020, Ken Tanzer wrote: > > There's one last piece of this query I'm clearly not getting though. > Where it says: > > from foo as f, jsonb_to_recordset(js) as t(key2 text) > > what is actually going on there? I keep reading this as a table foo (f) > cross-joined to a

Re: Extracting data from jsonb array?

On Mon, Dec 7, 2020 at 8:45 PM Tom Lane wrote: > Ken Tanzer writes: > > On Mon, Dec 7, 2020 at 8:16 PM Tom Lane wrote: > >> If foo.id is a primary key, it knows that the "group by" doesn't really > >> merge any rows of foo, so it lets you get away with that. I think this > >> is actually

Re: Extracting data from jsonb array?

This article might help understanding the reason - https://dba.stackexchange.com/questions/158015/why-can-i-select-all-fields-when-grouping-by-primary-key-but-not-when-grouping-b >From the postgres docs: "When GROUP BY is present, or any aggregate functions are present, it is not valid for the

Re: Extracting data from jsonb array?

Ken Tanzer writes: > On Mon, Dec 7, 2020 at 8:16 PM Tom Lane wrote: >> If foo.id is a primary key, it knows that the "group by" doesn't really >> merge any rows of foo, so it lets you get away with that. I think this >> is actually required by spec, but am too lazy to go check right now. > If

Re: Extracting data from jsonb array?

On Mon, Dec 7, 2020 at 8:16 PM Tom Lane wrote: > Ken Tanzer writes: > >> => select f.id, f.f1,f.f2,array_agg(t.key2) from foo as f, > >> jsonb_to_recordset(js) as t(key2 text) group by f.id; > > > After a little more thought and experimenting, I'm not so sure about this > > part. In

Re: Extracting data from jsonb array?

You can also do this: b2bcreditonline=# select f.id, f.f1, f.f2, (select array_agg(t.key2) from jsonb_to_recordset(js) as t(key2 text)) as key2s from foo as f; id |f1 | f2 | key2s +---++ 1 | My text 1 | My text 1a |

Re: Extracting data from jsonb array?

Ken Tanzer writes: >> => select f.id, f.f1,f.f2,array_agg(t.key2) from foo as f, >> jsonb_to_recordset(js) as t(key2 text) group by f.id; > After a little more thought and experimenting, I'm not so sure about this > part. In particular, I'm not clear why Postgres isn't complaining about > the

Re: Extracting data from jsonb array?

What am I missing? b2bcreditonline=# select * from foo; id | js |f1 | f2 ++---+ 1 | [{"key": "r1kval", "key2": "r1k2val"}, {"key":

Re: Extracting data from jsonb array?

On Mon, Dec 7, 2020 at 7:33 PM Ken Tanzer wrote: > > But this has a big advantage in that you can just add other fields to the > query, thusly: > > => select f.id, f.f1,f.f2,array_agg(t.key2) from foo as f, > jsonb_to_recordset(js) as t(key2 text) group by f.id; > id |f1 | f2 |

Re: Extracting data from jsonb array?

On Mon, Dec 7, 2020 at 7:12 PM Steve Baldwin wrote: > How about this: > > b2bcreditonline=# select f.id, array_agg(t.key2) from foo as f, > jsonb_to_recordset(js) as t(key2 text) group by f.id; > id | array_agg > + > 2 | {r2k2val,r2k2val2} > 1 |

Re: Extracting data from jsonb array?

How about this: b2bcreditonline=# select f.id, array_agg(t.key2) from foo as f, jsonb_to_recordset(js) as t(key2 text) group by f.id; id | array_agg + 2 | {r2k2val,r2k2val2} 1 | {r1k2val,r1k2val2} (2 rows) Steve On Tue, Dec 8, 2020 at 1:00 PM David G. Johnston

Re: Extracting data from jsonb array?

On Monday, December 7, 2020, Ken Tanzer wrote: > > > I'm of course very glad Postgresql has the ability to work with JSON at > all, but as I dig into it I'm kinda surprised at the level of complexity > needed to extract data in relatively simple ways. Hopefully eventually it > will seem simple

Re: Extracting data from jsonb array?

On Mon, Dec 7, 2020 at 5:20 PM Rob Sargent wrote: > > On 12/7/20 6:17 PM, David G. Johnston wrote: > > On Mon, Dec 7, 2020 at 6:13 PM Rob Sargent wrote: > >> >> postgres=# select id, array_agg(fa) from (select id, >> (jsonb_array_elements(js)->'key') as fa from foo) g group by id order by id;

Re: Extracting data from jsonb array?

On 12/7/20 6:17 PM, David G. Johnston wrote: On Mon, Dec 7, 2020 at 6:13 PM Rob Sargent > wrote: postgres=# select id, array_agg(fa) from (select id, (jsonb_array_elements(js)->'key') as fa from foo) g group by id order by id;  id | 

Re: Extracting data from jsonb array?

On Mon, Dec 7, 2020 at 6:13 PM Rob Sargent wrote: > > postgres=# select id, array_agg(fa) from (select id, > (jsonb_array_elements(js)->'key') as fa from foo) g group by id order by id; > id | array_agg > +-- > 1 | {"\"r1kval\"","\"r1kval2\""} > 2 |

Re: Extracting data from jsonb array?

OK, let me try asking again.  (I'm trying to actually get something that works.)  So given an example like this: CREATE TEMP TABLE foo (   id INTEGER,   js  JSONB ); INSERT INTO foo (id,js) VALUES (1, '[ {"key":"r1kval","key2":"r1k2val"}, {"key":"r1kval2","key2":"r1k2val2"} ]'); INSERT INTO

Re: Extracting data from jsonb array?

On Mon, Dec 7, 2020 at 4:00 PM David G. Johnston wrote: > On Mon, Dec 7, 2020 at 4:49 PM Ken Tanzer wrote: > >> >> >> On Mon, Dec 7, 2020 at 3:22 PM David G. Johnston < >> david.g.johns...@gmail.com> wrote: >> >>> On Mon, Dec 7, 2020 at 4:13 PM Steve Baldwin >>> wrote: >>> Try:

Re: Extracting data from jsonb array?

On Mon, Dec 7, 2020 at 4:49 PM Ken Tanzer wrote: > > > On Mon, Dec 7, 2020 at 3:22 PM David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> On Mon, Dec 7, 2020 at 4:13 PM Steve Baldwin >> wrote: >> >>> Try: >>> >>> select _message_body->'Charges'->>'Name' from ... >>> >> >> Not so

Re: Extracting data from jsonb array?

On Mon, Dec 7, 2020 at 3:22 PM David G. Johnston wrote: > On Mon, Dec 7, 2020 at 4:13 PM Steve Baldwin > wrote: > >> Try: >> >> select _message_body->'Charges'->>'Name' from ... >> > > Not so much..."Charges" is an array so "->>" doesn't do anything useful. > > The OP needs to use

Re: Extracting data from jsonb array?

On Mon, Dec 7, 2020 at 4:13 PM Steve Baldwin wrote: > Try: > > select _message_body->'Charges'->>'Name' from ... > Not so much..."Charges" is an array so "->>" doesn't do anything useful. The OP needs to use "json_array_elements" to navigate past the array and get to the next layer of the json

Re: Extracting data from jsonb array?

On Mon, Dec 7, 2020 at 3:12 PM Steve Baldwin wrote: > Try: > > select _message_body->'Charges'->>'Name' from ... > > Hi Steve. I tried that again, and that returns a NULL value for me. I believe that is because Charges holds an array of two elements, each of which has a Name element. Though

Re: Extracting data from jsonb array?

Try: select _message_body->'Charges'->>'Name' from ... Steve On Tue, Dec 8, 2020 at 9:58 AM Ken Tanzer wrote: > Hello. This is probably simple, but I'm having a hard time making use of > some json data, and hoping someone can help. > > Given some data that looks like this (I added a couple

Extracting data from jsonb array?

Hello. This is probably simple, but I'm having a hard time making use of some json data, and hoping someone can help. Given some data that looks like this (I added a couple of carriage returns for readability): SELECT _message_body->'Charges' FROM message_import_court_case where