New post
Question
· Apr 20, 2017

JSON_OBJECT issue

#Caché #JSON #SQL

Hi All,

I use SQL function JSON_OBJECT to get data as a JSON object.

However, sometimes I get error with function JSON_OBJECT when values contain [, ], { or }.

Error:

[SQLCODE: <-400>:<Fatal error occurred>]

Unexpected error occurred in JSON_OBJECT() function execution of <JSON_OBJECT>.%FromJSON().Parsing error

For example,

Query: SELECT JSON_OBJECT('idSQL':id, 'content':content) FROM DocBook.block

For demonstration, I used Management Portal, System Explorer – SQL, Execute Query

This text was in Content:

{&quot;Info&quot;:{&quot;Error&quot;:&quot;ErrorCode&quot;:&quot;5001&quot;,&quot;ErrorMessage&quot;:&quot;ERROR #5001: Cannot find Subject Area: &#39;SampleCube&#39;&quot;} } }

If I delete all { and } from Content, I don’t get this error

I used Version: Cache for Windows (x86-64) 2017.1 (Build 792U) Mon Mar 20 2017 19:13:14 EDT

Thanks,

Eremin Konstntin.

Discussion (9)0
Log in or sign up to continue
Sean Connelly · Apr 20, 2017

The error message is heavily escaped, it would look like this...

{"Info":{"Error":"ErrorCode":"5001","ErrorMessage":"ERROR #5001: Cannot find Subject Area: 'SampleCube'"} } }

This error is only raised in the %ParseStatement method of the %DeepSee.Query.Parser class.

I'm at the limits of what I know on DeepSee, but if I read this as it looks, there is a missing cube called SampleCube?

0 0
Eduard Lebedyuk · Apr 20, 2017

That's, I think is an unrelated issue. This SQL:

SELECT JSON_OBJECT('id': '{{}')

Also throws the same error:

[SQLCODE: <-400>:<Fatal error occurred>]
[%msg: <Unexpected error occurred in JSON_OBJECT() function execution of <JSON_OBJECT>.%FromJSON({{}).Parsing error :: Line 1 Offset 2>]

Seems like some data escaping is required.

Very simple escaping (this query executes successfully):

SELECT JSON_OBJECT('id': ' '||'{{}')
1 0
Константин Ерёмин · Apr 20, 2017

If I run the same query without the JSON_OBJECT function, I get data correctly from SQL table

I correctly got text from row where was error,  - {&quot;Info&quot;: {&quot;Error&quot;: {&quot;ErrorCode&quot;:&quot;5001&quot;,&quot;ErrorMessage&quot;:&quot;ERROR #5001: Cannot find Subject Area: &#39;SampleCube&#39;&quot;} } }

This text ( {"Info":{"Error":"ErrorCode":"5001","ErrorMessage":"ERROR #5001: Cannot find Subject Area: 'SampleCube'"} } } ) isn’t message about error, it is just text in SQL table (Now I am working on Cache documentation. These text was from Cache documentation table).

0 0
Sean Connelly · Apr 20, 2017

Just adding a space will prevent the value being non escaped...

SELECT JSON_OBJECT('id': ' {{}')

Which you can add to the end as well...

SELECT JSON_OBJECT('id': '{{} ')

Which basically suggests that if a value starts with a { and ends with a } then the JSON_OBJECT function assumes that it is a valid JSON object and does not escape it, for instance this...

SELECT JSON_OBJECT('id': '{"hello":"world"}')

will output this...

{"id":{"hello":"world"}}

In some ways I would say this is valid / wanted behaviour, except perhaps that there should be a special Caché type for raw json where there is then an implicit handling of that type in these functions.

An alternative workaround that works is to use the CONCAT function to append a trailing space...

SELECT JSON_OBJECT('id':{fn CONCAT('{{}',' ')})

Which produces...

{"id":"{{} "}

Which on the original query would need to be...

SELECT JSON_OBJECT('idSQL':id, 'content': {fn CONCAT(content,' ')} ) FROM DocBook.block

1 0