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?

Chris Saxon · Accepted Answer · 2019-02-21 17:11:26Z

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

answered Feb 21, 2019 at 17:11

Chris Saxon

10.2k1 gold badge33 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

qtwo Over a year ago

Brilliant, that fixes it, thank you! Is there a good source that you would recommend for reference for json manipulation in Oracle?

Chris Saxon Over a year ago

I published an overview of How to store, query, and create json documents in Oracle Database yesterday ;) If you want something more comprehensive, check out the JSON Developer's Guide

Collectives™ on Stack Overflow

Retrieving values from multiple json in Oracle

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related