Environment: I’m using Oracle 19c database, so I am using the JSON functions declared already in PLSQL (for instance JSON_TABLE).

What I am trying to do: I am trying to query JSON data from a CLOB column when there are multiple rows in the CLOB table

Problem: When I have one row in the base table (with the CLOB column containing the JSON data), I can query the JSON data successfully but when I add a second row to the base table my query fails with an ORA-01427: single-row subquery returns more than one row.

Example: I created a table with a CLOB to hold JSON data.

Create Table MY_RECEIVED_DATA
(LOAD_ID            NUMBER  GENERATED BY DEFAULT ON NULL AS IDENTITY,
   DATE_LOADED      DATE    DEFAULT SYSDATE NOT NULL,
   DATA_BUCKET      CLOB    CONSTRAINT check_json CHECK (DATA_BUCKET IS JSON))
LOB (DATA_BUCKET) STORE AS SECUREFILE (
  TABLESPACE  MYTAB
  ENABLE      STORAGE IN ROW
  CHUNK       8192
  RETENTION
  NOCACHE
  LOGGING)   
TABLESPACE MYTAB;

I then inserted 3 rows into the table showing the employees of Acme Inc.

insert into MY_RECIEVED_DATA (DATA_BUCKET)
VALUES ('[
    {   "displayName":  "McNubbins, Bubba",
        "employeeType":  "Minion",
        "givenName":  "Bubba",
        "company":  "Acme Inc"},
    {   "displayName":  "Blorgg, Gupda",
        "employeeType":  "Minion Supervisor",
        "givenName":  "Goopy",
        "company":  "Acme Inc"},
    {   "displayName":  "Zumba, Blippins",
        "employeeType":  "Overlord",
        "givenName":  "Blippy",
        "company":  "Acme Inc"}
    ]');
    Commit;

I then ran this select to see if I had any data and got this result

select * from MY_RECIEVED_DATA;

LOAD_ID DATE_LOADED          DATA_BUCKET
4       8/2/2022 1:45:16 PM  (CLOB)

I then ran this query to look at the JSON data in the DATA_BUCKET with these results

select *
from json_table((select DATA_BUCKET from MY_RECIEVED_DATA), '$[*]' 
     COLUMNS ("displayName", "employeeType","givenName","company")) jt 
group by "displayName","employeeType","givenName","company";

DISPLAYNAME       EMPLOYEETYPE       GIVENNAME    COMPANY
McNubbins, Bubba  Minion             Bubba        Acme Inc
Blorgg, Gupda     Minion Supervisor  Goopy        Acme Inc
Zumba, Blippins   Overlord           Blippy       Acme Inc

I then insert my second row of data

insert into MY_RECIEVED_DATA (DATA_BUCKET)
VALUES ('[
    {   "displayName":  "Corbin, Sammy",
        "employeeType":  "Jr. Minion",
        "givenName":  "Slimy",
        "company":  "Pipe Wrenches Llc."},
    {   "displayName":  "Zima, Dancy",
        "employeeType":  "Minion",
        "givenName":  "Dancy",
        "company":  "Pipe Wrenches Llc."},
    {   "displayName":  "Hoptom, Clarence",
        "employeeType":  "Overlord",
        "givenName":  "Sir",
        "company":  "Pipe Wrenches Llc."}
    ]');
    Commit;

I then re-ran this select to see if I had any data and got this result

select * from MY_RECIEVED_DATA;

LOAD_ID DATE_LOADED          DATA_BUCKET
4       8/2/2022 1:45:16 PM  (CLOB)
5       8/2/2022 2:10:34 PM  (CLOB)

So now I have two rows both containing JSON data in the DATA_BUCKET column. I then run this same query to get the JSON columns

select *
from json_table((select DATA_BUCKET from MY_RECIEVED_DATA), '$[*]' 
     COLUMNS ("displayName", "employeeType","givenName","company")) jt 
group by "displayName","employeeType","givenName","company";

When I run the select I get the following error:

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

The system will be updating the table several time a day and we will have to process each new row's JSON data.

Question: How can I query multiple rows of JSON data?

CodePudding user response：

Don't use a subquery; join the real table to the json_table clause:

select jt.*
from MY_RECEIVED_DATA mrd
cross apply json_table(
    mrd.DATA_BUCKET,
    '$[*]' 
    COLUMNS (
        "displayName", "employeeType","givenName","company"
    )
) jt

db<>fiddle

Not sure why you have the group-by clause; it works with or without it, but it isn't really adding anything. If you think you might have duplicates then you can use distinct instead of grouping.

I'd also suggest you give the columns non-quoted aliases, as in this db<>fiddle, but it depends how you'll use the result.