sql: limited applicability of jsonb_array_elements function due to lack of correlated SQs

[ms-mullins]

We're attempting to use the jsonb_array_elements function with CockroachDb 2.0 and it appears to generate an error. Here’s the test case to reproduce:

 create table test(
       id SERIAL,
       data jsonb,
       primary key (id)
    );
 
insert into test(data) values('{"name": "foo", "members": [{"name": "admin", "type": "USER"}]}');

select * from jsonb_array_elements(test.data->'groupMembers');

We getting: "SQL Error [42P01]: ERROR: no data source matches prefix: test". If we’re doing something incorrect, it’s unclear what that is.

cockroach version
Build Tag: v2.0.0
Build Time: 2018/04/04 14:43:38
Distribution: CCL
Platform: darwin amd64 (x86_64-apple-darwin17.3.0)
Go Version: go1.10.1
C Compiler: 4.2.1 Compatible Apple LLVM 9.0.0 (clang-900.0.39.2)
Build SHA-1: a6b498b
Build Type: development

[justinj]

Hi @ms-mullins,

I agree that error message is not particularly helpful, but what are you trying to achieve? If you want to return every single member of a members array, the following will work in Postgres:

select jsonb_array_elements(data->'members') from test;

but not in CockroachDB, since there's an implicit correlated subquery, which we don't support today. I'm not sure if there's a way in CockroachDB's current dialect to achieve this (though we're planning to support correlated subqueries in 2.1).

Can you outline what the output you're hoping for is, and maybe we can find a way to write a query to return it?

cc @knz, I think the root problem here is that the error message doesn't illustrate the fact that the query is actually wrong, or give a hint at how to fix it.

[knz]

I filed #24684 to address this (how to detect and report correlation). Justin can you bring that issue to our next roundtable?

[rnestertsov]

Usage of functions like jsonb_array_elements and others seems really limited to me right now, since CockroachDB doesn't support correlated subqueries.
Let's suppose I have following test data:

drop table if exists groups;
create table groups(
  id SERIAL,
  data jsonb,
  primary key (id)
);
 
insert into groups(data) values('{"name": "Group 1", "members": [{"name": "admin", "type": "USER"}, {"name": "user", "type": "USER"}]}');
insert into groups(data) values('{"name": "Group 2", "members": [{"name": "admin2", "type": "USER"}]}');

I want to output an expanded list of users with correspondent group name. My result set should look like:

role_name	user
Group 1	{"name": "admin", "type": "USER"}
Group 1	{"name": "user", "type": "USER"}
Group 2	{"name": "admin2", "type": "USER"}

In PostgreSQL I can use following query (here I specify explicit subquery):

SELECT
  g.data->>'name' AS group_name,
  jsonb_array_elements( (SELECT gg.data->'members' FROM groups gg WHERE gg.data->>'name' = g.data->>'name') )
FROM
  groups g
;

but in CockroachDB it doesn't work.
Could you please give some advice on how such type of queries can be written in CockroachDB?

[nvanbenschoten]

@rnestertsov you're correct that without correlated subqueries the query you posted won't work in Cockroach. This is unfortunate, and we're actively looking to address the issue by adding in correlated subquery support.

For now, you can try the following query, which acheives the same outcome (in a less efficient way):

SELECT DATA->'name' AS role_name,
       DATA->'members'->i AS user_name
FROM groups,
  (SELECT generate_series(0,
                            (SELECT max(json_array_length(DATA->'members')) - 1
                             FROM groups)::INT) AS i)
WHERE DATA->'members'->i IS NOT NULL;

[awoods187]

@jordanlewis and @andy-kimball i was expecting this to work post-Apply and I just checked on master (v19.1.0-beta.20190304-458-g70e3468) and it does not:

root@localhost:26257/tpcc> create table test(                                                                                                                                                                                                                                   id SERIAL,                                                                                                                                                                                                                                                               data jsonb,                                                                                                                                                                                                                                                              primary key (id)                                                                                                                                                                                                                                                      );
CREATE TABLE

Time: 1.85184438s

root@localhost:26257/tpcc> insert into test(data) values('{"name": "foo", "members": [{"name": "admin", "type": "USER"}]}');
INSERT 1

Time: 762.728077ms

root@localhost:26257/tpcc> select * from jsonb_array_elements(test.data->'groupMembers');
pq: no data source matches prefix: test

[jordanlewis]

@awoods187 yeah that one doesn't work because we still don't support the implicit lateral join thing - but the one where you reorder it to put the data source in the FROM (a valid workaround) does.

select jsonb_array_elements(data->'members') from test;

[justinj]

The original query in this issue isn't valid, even in Postgres:

postgres=# select * from jsonb_array_elements(test.data->'groupMembers');
ERROR:  missing FROM-clause entry for table "test"
LINE 1: select * from jsonb_array_elements(test.data->'groupMembers'...

Jordan's modified version is the right way to do this, and we support it.

[knz]

select * from jsonb_array_elements(test.data->'groupMembers');

This does not work in pg, however this does:

select * from test, jsonb_array_elements(test.data->'groupMembers');

however, it does not in cockroachdb.

This is because crdb does not know about the following rule: in a cross-join expression a,b, if b is a SRF applicaiton, then lateral should be implicitly assumed.

[awoods187]

Here is the related lateral join issue for tracking #24560