Skip to Main Content
  • Questions

  • JSON_QUERY and JSON_VALUE not able to handle more than 1 lakh characters

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Kumar.

Asked: January 23, 2018 - 3:53 am UTC

Last updated: March 07, 2018 - 2:50 pm UTC

Version: Oracle 12.1.x

Viewed 1000+ times

You Asked

JSON_QUERY and JSON_VALUE not able to handle more than 1 lakh characters. I've a big report (size can change at any time) and push it to oracle as JSON and parse there. I'm getting JSON Syntax Error, may be since the JSON is truncated to 4000 bytes. Is Oracle JSON feature not suited for big data like this? Please help Tom!

PL/SQL code looks like: 

V_JSON_DATA CLOB; -- Converted from BLOB,which comes as function parameter
V_SQL CLOB;

V_SQL := 'SELECT JSON_QUERY('''' || V_JSON_DATA || ''',''$.' || KEY || ''') FROM DUAL';
EXECUTE IMMEDIATE V_SQL;


and Chris said...

You're going to need to be more specific about what you're doing.

This example with a JSON document containing 100,009 characters works just fine:

declare
  jdoc clob;
  res  varchar2(200);
begin
  dbms_lob.createtemporary(jdoc,true);
  
  jdoc := '{"v0": 0';
  for i in 1 .. 1000 loop
    dbms_lob.writeappend(jdoc,100,', "v' || i || '": "' || rpad('x', 90, 'x') || '"');
  end loop;
  dbms_lob.writeappend(jdoc,1,'}');
  
  select json_query(jdoc, '$.v1' returning varchar2 with wrapper)
  into   res
  from   dual;
  
  dbms_output.put_line(dbms_lob.getlength(jdoc) || '; ' || res);
  
  dbms_lob.freetemporary(jdoc);
end;
/

100009; ["xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"]

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Markus, March 06, 2018 - 2:23 pm UTC

I think the question refers to the fact that json_query returns a varchar2(4000) by default.
There is the RETURNING clause which allows to change the data type to varchar2 smaller than 4000, but not to CLOB, which would be necessary for large return values.
Is there an alternative to json_query? Maybe in apex_json?
Chris Saxon
March 07, 2018 - 2:50 pm UTC

You can specify the varchar2 length in the returning clause if you want:

with rws as (
  select '{"attr":"valuuuuuuuuue"}' j 
  from   dual
)
  select json_value(j, '$.attr' returning varchar2(10)) too_short,
         json_value(j, '$.attr' returning varchar2(20)) long_enough
  from   rws;

TOO_SHORT   LONG_ENOUGH     
<null>      valuuuuuuuuue


Notice too_short is null because of the default "null on error" clause.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.