Retrieving values from multiple json in Oracle

Question

I'd like to query some data stored in json in a relational Oracle table.

create table Foo (
  id VARCHAR2(40) not null,
  data CLOB,
  constraint foo_pk primary key (id),
  constraint foo_json_chk check (data is json)
);

insert into Foo (id, data) values ('1', '{"id": "1", "name": "bar1"}');
insert into Foo (id, data) values ('2', '{"id": "2", "name": "bar2"}');

I can query from one json using json_table:

select name from json_table(
  (select data from Foo where id='1'),
  '$'
  COLUMNS (
    name PATH '$.name'
    )
);

Gives

+------+
| name |
+------+
| bar1 |
+------+

If we try to query from multiple rows:

select name from json_table(
  (
    select data from Foo --where id='1'
  ),
  '$'
  COLUMNS (
    name PATH '$.name'
    )
);

I'd like to obtain all the values matching the json path. For the data stored above this would mean:

+------+
| name |
+------+
| bar1 |
| bar2 |
+------+

Later on I will combine this query in a join with other tables in the system.

Unfortunately I get instead the following error:

ORA-01427: single-row subquery returns more than one row

I've tried different combinations of NESTED PATH and of JSON_ARRAYAGG but no luck so far.

How can I extract values from several rows using the same json path?

Answer 1

You don't need a subquery in the json_table expression. List your source table in the from clause:

select name 
from   foo, json_table(
  data , '$'
  columns (
    name PATH '$.name'
    )
);

NAME   
bar1   
bar2