Error using jsonb_array_elements

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.

Hi @mmullins,

The problem is that you haven’t added test as a data source - this is invalid SQL. Tables must be present in a FROM clause to be referenced.

In this case, I suggest using a subquery to resolve the issue. Try something like:

SELECT * FROM jsonb_array_elements((SELECT data->'groupMembers' FROM test));