Hey everyone!
It sounds like one of these cases where somebody wants to pass in a nested JSON
array with many small JSON documents inside them, the joy of hierarchical
structures.
We often see folks coming along with a gigantic JSON document which in itself
is an array of smaller JSON documents, e.g.:
[
{
"array": [
1,
2,
3
],
"boolean": true,
"color": "gold",
"null": null,
"number": 123,
"object": {
"a": "b",
"c": "d"
},
"string": "Hello World"
},
{
"array": [
1,
2,
3
],
"boolean": true,
"color": "gold",
"null": null,
"number": 123,
"object": {
"a": "b",
"c": "d"
},
"string": "Hello World"
}
]
In the above JSON doc there are essentially two separate JSON documents hiding
in an encompassing array, which, to some extend unfortunately, is valid JSON as
well (all you have to do is to add a [ in front and ] in the back and call it a
day, rather than actually think about what you are doing.
We had folks rocking up with a GB sized JSON doc in this array form housing
thousands of actual JSON docs inside them.
The solution for that particular problem is not to store one single row with
one gigantic JSON doc in GB form but, of course, to store all of the individual
JSON documents as separate rows.
I have a feeling they are trying to do something similar here, instead of
calling the PL/SQL function with the actual documents, they just toss the
entire array in there, instead they should be calling the PL/SQL
function/procedure with the individual JSON docs directly.
And if it is not that case, I would be really interested to see what JSON
document out there is legitimate 32k long.
Not saying they don’t exist, just saying that we haven’t seen them yet.
Thx and happy weekend,
---------------
Gerald Venzl | Distinguished Product Manager
Email: gerald.venzl@xxxxxxxxxx<mailto:gerald.venzl@xxxxxxxxxx> | Phone:
+1.650.633.0085<tel:+16506330085>
Oracle ST & Database Development
400 Oracle Parkway | Redwood Shores | 94065 | USA
On Mar 26, 2021, at 13:01, Kim Berg Hansen
<kibeha@xxxxxxxxx<mailto:kibeha@xxxxxxxxx>> wrote:
Okay, I found that. Yes, the max return-value length of SQL/JSON functions.
I'll have to interpret that as the return value of JSON functions that retrieve
individual elements of JSON.
Because also in the JSON docs are written:
Use BLOB (binary large object) or CLOB (character large object) storage if you
know that you have some JSON documents that are larger than 32767 bytes (or
characters)Foot
1<https://docs.oracle.com/en/database/oracle/oracle-database/19/adjsn/overview-of-storage-and-management-of-JSON-data.html#fnsrc_d6187e110>.
https://docs.oracle.com/en/database/oracle/oracle-database/19/adjsn/overview-of-storage-and-management-of-JSON-data.html#GUID-26AB85D2-3277-451B-BFAA-9DD45355FCC7
It looks to me that it is definitely meant to be able to handle larger than 32K.
I think I'll try and test it tomorrow. I feel it should be possible :-)
If you're on Twitter, try to tweet to Beda Hammerschmidt:
https://twitter.com/bch_t<https://urldefense.com/v3/__https://twitter.com/bch_t__;!!GqivPVa7Brio!JQCKlbmKIF2MzdMIaY256C00jQmC9Q-mk4YWKsxcyjrx7H-avhncFJV4hlZ87yYWZqU$>
He'll be able to answer for certain.
Cheerio
/Kim
On Fri, Mar 26, 2021 at 8:43 PM Andrew Kerber
<andrew.kerber@xxxxxxxxx<mailto:andrew.kerber@xxxxxxxxx>> wrote:
ITs in the oracle json developers guide.
* General
* Number of nesting levels for a JSON object or array: 1000, maximum.
* JSON field name length: 32767 bytes, maximum.
* SQL/JSON functions
* Return-value length: 32767 bytes, maximum.
* Path length: 4K bytes, maximum.
* Number of path steps: 65535, maximum.
* Simplified JSON syntax
* Path length: 4K bytes, maximum.
* Path component length: 128 bytes, maximum.
* JSON search index
* Field name length: 255 bytes, maximum. If a document has a field name
longer than 255 bytes then it might not be completely indexed. In that case, an
error is recorded in database view CTX_USER_INDEX_ERRORS.
On Fri, Mar 26, 2021 at 2:29 PM Kim Berg Hansen
<kibeha@xxxxxxxxx<mailto:kibeha@xxxxxxxxx>> wrote:
Is it necessary for them to parse the JSON CLOBs into JSON object types?
Could they potentially be better off using the native SQL functions like
JSON_TABLE and JSON_QUERY to retrieve data from the JSON values?
Having said that, JSON_ELEMENT_T.PARSE is an overloaded function accepting both
VARCHAR2, CLOB and BLOB.
Are they passing the CLOB directly to PARSE, or could they somehow be hitting
an implicit conversion that makes the code choose the VARCHAR2 version of PARSE?
Where do you see the 32k limit in the docs? (It's probably there, I just can't
find it at the moment ;-)
Cheerio
/Kim
Regards
Kim Berg Hansen
Senior Consultant at Trivadis
Oracle ACE Director
Author of Practical Oracle
SQL<https://urldefense.com/v3/__https://www.apress.com/gp/book/9781484256169__;!!GqivPVa7Brio!JQCKlbmKIF2MzdMIaY256C00jQmC9Q-mk4YWKsxcyjrx7H-avhncFJV4hlZ8EsSw13o$>
http://www.kibeha.dk<https://urldefense.com/v3/__http://www.kibeha.dk__;!!GqivPVa7Brio!JQCKlbmKIF2MzdMIaY256C00jQmC9Q-mk4YWKsxcyjrx7H-avhncFJV4hlZ8_6S4mNE$>
kibeha@xxxxxxxxx<mailto:kibeha@xxxxxxxxx>
@kibeha<https://urldefense.com/v3/__http://twitter.com/kibeha__;!!GqivPVa7Brio!JQCKlbmKIF2MzdMIaY256C00jQmC9Q-mk4YWKsxcyjrx7H-avhncFJV4hlZ8HKzNbyQ$>
On Fri, Mar 26, 2021 at 8:06 PM Andrew Kerber
<andrew.kerber@xxxxxxxxx<mailto:andrew.kerber@xxxxxxxxx>> wrote:
I really dont know much at all about JSON, though we have a group that is using
that format to store data in CLOBs in the database. They are running into a
problem that seems to be a hard oracle limit.
They have a bunch of JSON CLOBs larger than 32k. I havent asked them how they
created them, But anyway, they are using a stored procedure called
JSON_ELEMENT_T.PARSE. And they are getting a parse error whenever they try to
parse a json document larger than 32k. Looking at the documentation for JSON,
its pretty clear they are hitting a hard string length limit of 32k.
They tried to tell me its an Oracle bug, but reading through the documentation,
I can see that this 32k string size limit is pretty well documented. In any
case, does anyone know of a way around this? My suggestion was to pull out the
JSON in 32k chunks and assemble it in a language that can deal with JSON larger
than 32k.
--
Andrew W. Kerber
'If at first you dont succeed, dont take up skydiving.'
--
Andrew W. Kerber
'If at first you dont succeed, dont take up skydiving.'
