Is there a way to defermine that column is a JSON_OBJECT

Question

Question

Eduard Lebedyuk · Jul 9, 2020

I got a resultset, and some columns might be a JSON_OBJECT. Is there a way (based on metadata) to determine that the column was formed from JSON_OBJECT function?

set rs = ##class(%SQL.Statement).%ExecDirect(,"SELECT 1 colA, JSON_OBJECT('id':1) col2")
do rs.%Display()

I do not want to try parsing json.

Discussion (5)0

Log in or sign up to continue

Robert Cemper · Jul 9, 2020

I didn't meet that.

Basically it's %String or a %Stream based on size.
I'd suggest having a private data type.
This allows you also to have all tricky LogicalToODBC, .... under control. (and no MAXLEN for %String)
And mapping it to %ALL or naming it %ZstringJSON or similar makes it public and update-restistant

1 0

Vitaliy Serdtsev · Jul 9, 2020

select *,
case when a is JSON then 1 else 0 end aa,
case when b is JSON then 1 else 0 end bb,
case when c is JSON then 1 else 0 end cc

from (SELECT 1 a, JSON_OBJECT('id':1) b, '{"id":1}' c)

2 0

score 0 · Answer 1 · 2020-07-09T12:23:04-04:00

Eduard Lebedyuk · Jul 9, 2020

JSON_OBJECT would produce %String still.

0 0

score 0 · Answer 2 · 2020-07-09T15:50:48-04:00

Robert Cemper · Jul 9, 2020

Got you!
Something like $isJSON() ? like $isobject() or $listvalid()

0 0

score 0 · Answer 3 · 2020-07-09T17:17:16-04:00

Great stuff @Vitaliy.Serdtsev
https://docs.intersystems.com/iris20192/csp/docbook/Doc.View.cls?KEY=RSQL_json

everlasting learning!