JSON_VALUE PATH EROR "JSON path is not properly formatted"-CodePudding

I have a data table. I have a colon from the database. This column comes in the following format.

[{"ID":1,"Change":true,"Painted":false},{"ID":2,"Change":true,"Painted":false}]

I wanted to split this array into 3 different columns. I want the column with the ID as an array. So I used '$..ID' which uses JSON_VALUE. But I am getting error.

JSON_VALUE(X.VALUE,'$..ID') as ID,

CodePudding user response：

Try using one dot between $ and ID in your JSON path.

JSON_VALUE(X.VALUE,'$.ID') as ID,

CodePudding user response：

I wanted to split this array into 3 different columns

SELECT jsontable.num, jsontable.id, jsontable.change, jsontable.painted
FROM test
CROSS JOIN JSON_TABLE(test.value,
                      '$[*]' COLUMNS (num FOR ORDINALITY,
                                      id INT PATH '$.ID',
                                      `change` TEXT PATH '$.Change',
                                      painted TEXT PATH '$.Painted')) jsontable

Pay attention - change and painted are strings, not boolean. If you need in BOOLEAN value then use CASE operator or IF() function additionally. Or you may use not TEXT but JSON datatype.

I want the column with the ID as an array.

SELECT JSON_EXTRACT(value, '$[*].ID')
FROM test

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=acad55b5f28330cfbeb08a8a3006eec1