Re: DB2 JSON_TABLE strict mode

Well, I notice I missed a comma off the manuDate Column and capital really
shouldn't have an o in it...

But that's not really the point.

I guess the simpler version of my question is:

*How can I identify if someone sends me an element in a JSON document that
I wasn't expecting ( because it might simply be a typo )*

I want to treat missing elements as null but identify unexpected elements.

I believe there is such a concept of a JSON schema, but I'm hoping to find
a way of doing this just using the new DB2 JSON functions.

From what I've read of JSON_TABLE and it's options ( such as NULL ON EMPTY

etc ) it is about looking for a specific element using a path and what to
do if it is not found or invalid, as opposed to parsing the document and
then trying to do something with each element found, which is kind of
approaching it from the other way around.

So, I'm really just trying to find out if there is an answer to my blue
italic question which doesn't involve passing every element every time but
with null values if necessary ( is this the norm? )

thanks again,
Craig

On 24 April 2018 at 16:48, Craig Richards <craig@xxxxxxxxxxxxxxxx> wrote:

Hi All,

I've written quite a lot of code using the DB2 JSON functions and it works
pretty well.

I've been using JSON_TABLE in lax mode and checking null indicators for
any mandatory keys.

But I've been concerned that with this approach, any misspelled optional
keys just get lost.

So I've started trying to use strict mode.
But unless I am missing something, I think that strict mode insists on all
specified keys to be present in the object.

Does anyone know if there is a way of using JSON_TABLE() such that absent
keys are just treated as null but misspelled keys are reported as errors?

For example:

I would like:

Select t1.ItemRef,
t1.ManuDate,
t1.AltRef,
t1.Grade
From WsInq00 t2,
JSON_TABLE(
t2.RqsText,
'strict $'
ERROR ON ERROR
COLUMNS(
ItemRef CHAR( 20 ) PATH 'strict $.itemRef',
ManuDate CHAR( 10 ) PATH 'strict $.manuDate'
AltRef CHAR( 30 ) PATH 'strict $.altRef',
Grade CHAR( 3 ) PATH 'strict $.grade'
)
) as t1
Where t2.UniqueID = 100982;


to accept this:
{

"itemRef": "SRSWLVB5GZWYS33LP9MQ",
"grade": "8EY"
}

because all *present *fields are correct keys

but reject this:
{

"itemRef": "SRSWLVB5GZWYS33LP9MQ",
"Grade": "8EY"
}


Because the Grade key is spelled with a capitol G and it should not be.

Is there a way of achieving this?

Thanks kindly,
Craig