CODEWITHSUNDEEP
mysqlmysql-jsonjson-extract
Senthilnathan periyasamy
Senthilnathan periyasamy

Reputation: 11

How to extract array values from mysql json field and return null or default if path does not exist?

I have this json in a mysql column.

{"items": [
    {
         "name": "a1",
         "details": {"value": 1}
    },
    {
         "name": "a2",
         "details": {"value": 2}
    },
    {
         "name": "a3"
    },
    {
         "name": "a4",
         "details": {"value": 4}
    },
 ]
}

As you can see details is an optional field for each item. when i try to extract value from this column using JSON_EXTRACT(column, '$.items[*].details.value'), i get [1,2,4] but i would like to get [1,2,null,4] instead. If the path does not exist, i would like to get null or any default value. Is it possible using mysql JSON_EXTRACT?

Upvotes: 1

Views: 336

Answers (0)

Related Questions