# Database Query JSON Default Function (JSON\_EXTRACT)

**URL:** <https://forum.inductiveautomation.com/t/database-query-json-default-function-json-extract/95789>\
**Category:** Ignition\
**Tags:** python, database, ignition81, scripting\
**Created:** [September 10, 2024, 12:17pm UTC](https://forum.inductiveautomation.com/t/database-query-json-default-function-json-extract/95789 "2024-09-10T12:17:23Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![illank86](https://avatars.discourse-cdn.com/v4/letter/i/c77e96/32.png) [@illank86](https://forum.inductiveautomation.com/u/illank86)\
**Post date:** [September 10, 2024, 12:17pm UTC](https://forum.inductiveautomation.com/t/database-query-json-default-function-json-extract/95789/1 "2024-09-10T12:17:23Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Benjamin\_Furlani](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/benjamin_furlani/32/66084_2.png) [@Benjamin\_Furlani](https://forum.inductiveautomation.com/u/Benjamin_Furlani)\
**Post date:** [September 10, 2024, 2:28pm UTC](https://forum.inductiveautomation.com/t/database-query-json-default-function-json-extract/95789/2 "2024-09-10T14:28:53Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![illank86](https://avatars.discourse-cdn.com/v4/letter/i/c77e96/32.png) [@illank86](https://forum.inductiveautomation.com/u/illank86)\
**Post date:** [September 10, 2024, 2:39pm UTC](https://forum.inductiveautomation.com/t/database-query-json-default-function-json-extract/95789/3 "2024-09-10T14:39:56Z")

</div>

I have tried something like this on SQLite

| id | json\_column |
| --- | --- |
| 1 | NULL |
| 2 | NULL |
| 3 | [{"name": "jhon", "age": 40}] |

```python
SELECT JSON_EXTRACT(json_column, '$') AS jsonData FROM table WHERE id = 3

```

its working.

But when i query :

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

```

the json data that has id=3 become 0

---

<div class="post-metadata">

**Author:** ![Benjamin\_Furlani](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/benjamin_furlani/32/66084_2.png) [@Benjamin\_Furlani](https://forum.inductiveautomation.com/u/Benjamin_Furlani)\
**Post date:** [September 10, 2024, 2:49pm UTC](https://forum.inductiveautomation.com/t/database-query-json-default-function-json-extract/95789/4 "2024-09-10T14:49:33Z")

</div>

When you just query [json\_column] what do you get?

---

<div class="post-metadata">

**Author:** ![illank86](https://avatars.discourse-cdn.com/v4/letter/i/c77e96/32.png) [@illank86](https://forum.inductiveautomation.com/u/illank86)\
**Post date:** [September 10, 2024, 2:56pm UTC](https://forum.inductiveautomation.com/t/database-query-json-default-function-json-extract/95789/5 "2024-09-10T14:56:36Z")

</div>

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

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

```

Thanks @Benjamin_Furlani

---

<div class="post-metadata">

**Author:** ![Benjamin\_Furlani](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/benjamin_furlani/32/66084_2.png) [@Benjamin\_Furlani](https://forum.inductiveautomation.com/u/Benjamin_Furlani)\
**Post date:** [September 10, 2024, 2:57pm UTC](https://forum.inductiveautomation.com/t/database-query-json-default-function-json-extract/95789/6 "2024-09-10T14:57:27Z")

</div>

Perfect! Glad you got it working