Обсуждение: Extracting data from jsonb array?

Try:

select _message_body->'Charges'->>'Name' from ...

--

AGENCY Software  

A Free Software data system

By and for non-profits

http://agency-software.org/

https://demo.agency-software.org/client

ken.tanzer@agency-software.org

(253) 245-3801

Subscribe to the mailing list to

learn more about AGENCY or

follow the discussion.

Try:

select _message_body->'Charges'->>'Name' from ...

On Mon, Dec 7, 2020 at 4:13 PM Steve Baldwin <steve.baldwin@gmail.com> 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 where ->>'Name' will then work.

On Mon, Dec 7, 2020 at 3:22 PM David G. Johnston <david.g.johnston@gmail.com> wrote:

On Mon, Dec 7, 2020 at 4:13 PM Steve Baldwin <steve.baldwin@gmail.com> 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 where ->>'Name' will then work.

Thank you David.  I had tried that function without much luck.  But with your inspiration, I made progress and got to this:

select _message_exchange_id,jsonb_array_elements(_message_body->'Charges')->>'Name' FROM message_import_court_case WHERE _message_exchange_id = 1296;
 _message_exchange_id |                  ?column?                  
----------------------+--------------------------------------------
                 1296 | Possession Of Burglary Tools
                 1296 | Burglary In The Second Degree (Commercial)
(2 rows)

But what I really want is one line per message, with the charges in an array.  I can't seem to find the right syntax to make this work:

=> select _message_exchange_id,array_agg(jsonb_array_elements(_message_body->'Charges')->>'Name') FROM message_import_court_case WHERE _message_exchange_id = 1296;
ERROR:  column "message_import_court_case._message_exchange_id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select _message_exchange_id,array_agg(jsonb_array_elements(_...
               ^

=> select _message_exchange_id,array_agg(jsonb_array_elements(_message_body->'Charges')->>'Name') FROM message_import_court_case WHERE _message_exchange_id = 1296 GROUP BY 1;
ERROR:  set-valued function called in context that cannot accept a set

=> select _message_exchange_id,(SELECT array_agg(jsonb_array_elements(_message_body->'Charges')->>'Name')) FROM message_import_court_case WHERE _message_exchange_id = 1296 GROUP BY 1;

ERROR:  set-valued function called in context that cannot accept a set

=> select _message_exchange_id,(SELECT array_agg(jsonb_array_elements(_message_body->'Charges')->>'Name')) FROM message_import_court_case WHERE _message_exchange_id = 1296;
ERROR:  column "message_import_court_case._message_exchange_id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select _message_exchange_id,(SELECT array_agg(jsonb_array_el...

On Mon, Dec 7, 2020 at 4:49 PM Ken Tanzer <ken.tanzer@gmail.com> wrote:

On Mon, Dec 7, 2020 at 3:22 PM David G. Johnston <david.g.johnston@gmail.com> wrote:

On Mon, Dec 7, 2020 at 4:13 PM Steve Baldwin <steve.baldwin@gmail.com> 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 where ->>'Name' will then work.

Thank you David.  I had tried that function without much luck.  But with your inspiration, I made progress and got to this:

select _message_exchange_id,jsonb_array_elements(_message_body->'Charges')->>'Name' FROM message_import_court_case WHERE _message_exchange_id = 1296;
 _message_exchange_id |                  ?column?                  
----------------------+--------------------------------------------
                 1296 | Possession Of Burglary Tools
                 1296 | Burglary In The Second Degree (Commercial)
(2 rows)

But what I really want is one line per message, with the charges in an array.  I can't seem to find the right syntax to make this work:

=> select _message_exchange_id,array_agg(jsonb_array_elements(_message_body->'Charges')->>'Name') FROM message_import_court_case WHERE _message_exchange_id = 1296;
ERROR:  column "message_import_court_case._message_exchange_id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select _message_exchange_id,array_agg(jsonb_array_elements(_...
               ^

=> select _message_exchange_id,array_agg(jsonb_array_elements(_message_body->'Charges')->>'Name') FROM message_import_court_case WHERE _message_exchange_id = 1296 GROUP BY 1;
ERROR:  set-valued function called in context that cannot accept a set

=> select _message_exchange_id,(SELECT array_agg(jsonb_array_elements(_message_body->'Charges')->>'Name')) FROM message_import_court_case WHERE _message_exchange_id = 1296 GROUP BY 1;

ERROR:  set-valued function called in context that cannot accept a set

=> select _message_exchange_id,(SELECT array_agg(jsonb_array_elements(_message_body->'Charges')->>'Name')) FROM message_import_court_case WHERE _message_exchange_id = 1296;
ERROR:  column "message_import_court_case._message_exchange_id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select _message_exchange_id,(SELECT array_agg(jsonb_array_el...

Sub-queries are a simple solution to get around the "set-valued function" restriction.

The more direct way is to place the set-valued function in the FROM clause where it wants to be, by using LATERAL (the keyword itself can be implied when dealing with functions)

select array_agg(e->>'key') from (values ('[{"key":"val"},{"key":"val2"}]'::jsonb)) val (v), jsonb_array_elements(v) jae (e)

David J.

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 | {"\"r2kval\"","\"r2kval2\""}
(2 rows)

I think the quotes are a fault of example data?  

On 12/7/20 6:17 PM, David G. Johnston wrote:

On Mon, Dec 7, 2020 at 6:13 PM Rob Sargent <robjsargent@gmail.com> 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 | {"\"r2kval\"","\"r2kval2\""}
(2 rows)

I think the quotes are a fault of example data?  

The quotes are the fault of the query author choosing the "->" operator instead of "->>".

David J.

With that correction OP might have an answer?

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)

Upgrade to v12+ for access to simpler/cleaner.  

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     |     array_agg      
----+-----------+------------+--------------------
  2 | My Text 2 | My Text 2a | {r2k2val,r2k2val2}
  1 | My Text 1 | My Text 1a | {r1k2val,r1k2val2}
(2 rows)

-- 

AGENCY Software  

A Free Software data system

By and for non-profits

http://agency-software.org/

https://demo.agency-software.org/client

ken.tanzer@agency-software.org

(253) 245-3801

Subscribe to the mailing list to

learn more about AGENCY or

follow the discussion.

Ken Tanzer <ken.tanzer@gmail.com> 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 f1 and f2 fields.  (It's not giving the "must appear in the GROUP BY
> clause or be used in an aggregate function" error that I would expect, and
> that I am getting when I try to apply this to my real query.)

> Can anyone explain to me why those fields don't need to be grouped?  Thanks.


If foo.id isn't a primary key, then I'm confused too.  Can we see the
full declaration of the table?

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.

Ken Tanzer <ken.tanzer@gmail.com> writes:
> On Mon, Dec 7, 2020 at 8:16 PM Tom Lane <tgl@sss.pgh.pa.us> 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 I do that without the Primary Key, it does indeed complain about f1 & f2
> not being grouped.  But what is the "It" in "it lets you get away with
> that" referring to?

Sorry I was vague there, it's the parse analysis phase that understands
that "GROUP BY a primary key" should be treated as allowing any column of
that pkey's table to be referenced without also explicitly grouping by
that other column.  If you then join to some other table, the free pass
doesn't extend to the other table.
 

-- 

Ken Tanzer <ken.tanzer@gmail.com> 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 (t).  But that
> doesn't seem right, because rows from t are only joining with matching rows
> from f, rather than all of them.  Is there some unspoken implicit logic
> going on here, or something else entirely that is going over my head?

There's an implicit LATERAL there:

  ... from foo as f, lateral jsonb_to_recordset(js) as t(key2 text)

ie, for each row of foo, extract the foo.js column and evaluate
jsonb_to_recordset(js) --- which, in this case, produces multiple
rows that are joined to the original foo row.  This is again a
SQL-ism.  I don't particularly care for their choice to allow
LATERAL to be implicit for function-call-like FROM items,
because it seems pretty confusing; but the spec is the spec.

[ thinks for a bit... ]  Again, I'm too lazy to go digging in
the spec's dense verbiage at this hour, but I'm vaguely recalling
that they may only require this behavior for the one case of
the function being UNNEST().  I think it was our choice to allow
it to work like that for any set-returning function.