Database Query JSON Default Function (JSON_EXTRACT)

Ignition
, , ,
1

Hi All,

i have problem with Database query

How to use Database JSON default function in Ignition, for example in SQLite, we can query JSON data with JSON_EXTRACT(data, '$'), but when i apply it in DB Browser or Scripting, it will return 0.0

Thanks

2

I believe since Ignition is using JBDC drivers for sql, they cannot return complex types. For example you can't return a type of uuid[] in postgresql you instead have to convert it to a string and then manipulate the data with scripting. So if you attempt to return data as a string in your SQLExpress using either cast or convert. and then utilize python for converting back to json and getting data out that way.

3

I have tried something like this on SQLite

id json_column
1 NULL
2 NULL
3 [{"name": "jhon", "age": 40}] 
SELECT JSON_EXTRACT(json_column, '$') AS jsonData FROM table WHERE id = 3

its working.

But when i query :

SELECT JSON_EXTRACT(json_column, '$') AS jsonData FROM table

the json data that has id=3 become 0

4

When you just query [json_column] what do you get?

5

OK i think i got it working,

i need to handle the NULL value which is not JSON valid format

so what i did was

SELECT 
 CASE WHEN json_column IS NULL
 	THEN "NULL"
 	ELSE JSON_EXTRACT(json_column, '$')
 END AS json_data
 FROM table

Thanks @Benjamin_Furlani

6

Perfect! Glad you got it working