Fetching object multiple levels in JSON array using JSON_QUERY in MariaDB do not work-CodePudding

I have the following table in a mariadb database

| model_id | model_name |model_json |

then on the field model_json I have the following json

{"class":"GraphLinksModel",
"linkFromPortIdProperty":"fromPort","linkToPortIdProperty":"toPort","nodeDataArray": [
{"category":"Start","text":"Start","key":-1,"loc":"-869.6666259765625 171.66668701171875"},
{"category":"Step","text":"knock the door","key":-2,"loc":"-618.9999389648438 169.00001525878906"},
{"category":"Step","text":"open the door","key":-3,"loc":"-337.6666259765625 177"},
{"category":"Step","text":"kick the guy","key":-4,"loc":"93 189"},
{"category":"End","text":"End","key":-5,"loc":"117.0001220703125 638.3333740234375"},
{"category":"Conditional","text":"is the guy dead?","key":-6,"loc":"78.333251953125 321"},
{"category":"Step","text":"get the body","key":-7,"loc":"122.3333740234375 522.3333740234375"}
],
  "linkDataArray": [
{"from":-6,"to":-4,"fromPort":"R","toPort":"R","visible":true,"points":[211.7525634765625,321,221.7525634765625,321,221.7525634765625,189,188.43417358398438,189,155.11578369140625,189,145.11578369140625,189],"text":"no"},
{"from":-6,"to":-7,"fromPort":"B","toPort":"T","visible":true,"points":[78.333251953125,354.6254486083985,78.333251953125,364.6254486083985,78.333251953125,429.6355491638184,122.3333740234375,429.6355491638184,122.3333740234375,494.64564971923824,122.3333740234375,504.64564971923824]},
{"from":-7,"to":-5,"fromPort":"B","toPort":"T","points":[122.3333740234375,540.0210983276367,122.3333740234375,550.0210983276367,122.3333740234375,573.302236175537,117.0001220703125,573.302236175537,117.0001220703125,596.5833740234375,117.0001220703125,606.5833740234375]},
{"from":-1,"to":-2,"fromPort":"R","toPort":"L","points":[-832.9166259765625,171.66668701171875,-822.9166259765625,171.66668701171875,-757.1020774841309,171.66668701171875,-757.1020774841309,169.00001525878906,-691.2875289916992,169.00001525878906,-681.2875289916992,169.00001525878906]},
{"from":-2,"to":-3,"fromPort":"R","toPort":"L","points":[-556.7123489379883,169.00001525878906,-546.7123489379883,169.00001525878906,-476.495418548584,169.00001525878906,-476.495418548584,177,-406.2784881591797,177,-396.2784881591797,177]},
{"from":-3,"to":-4,"fromPort":"R","toPort":"L","points":[-279.0547637939453,177,-269.0547637939453,177,-119.08527374267578,177,-119.08527374267578,189,30.88421630859375,189,40.88421630859375,189]},
{"from":-4,"to":-6,"fromPort":"B","toPort":"T","points":[93,206.68772430419924,93,216.68772430419924,93,247.03113784790042,78.333251953125,247.03113784790042,78.333251953125,277.3745513916016,78.333251953125,287.3745513916016]}
]}

doing the following query I got the result of the node 'nodeDataArray' which is ok

SELECT JSON_QUERY(model_json,'$.nodeDataArray') AS jsondata FROM pr_models WHERE  model_id = 2

now the problem comes when I want to extract the "category" from the node, like this:

SELECT JSON_QUERY(model_json,'$.nodeDataArray.category') AS jsondata  FROM pr_models WHERE  model_id = 2

result [NULL]

I did try JSON_VALUE thinking maybe the scalar but the result still the same

SELECT JSON_VALUE(model_json,'$.nodeDataArray.category') AS jsondata  FROM pr_models WHERE  model_id = 2

result [NULL]

the closest result I am getting is using this

SELECT JSON_VALUE(model_json,'$.nodeDataArray[0].category') AS jsondata  FROM pr_models WHERE  model_id = 2

result: jsondata Start, but it is just the first one. I need all of this

I did try my query using https://www.jsonquerytool.com/ and it seems to be correct

$.nodeDataArray.*.category

result

[
    "Start",
    "Step",
    "Step",
    "Step",
    "End",
    "Conditional",
    "Step"
]

but if I try the same in mariadb it wont work SELECT JSON_VALUE(model_json,'$.nodeDataArray..category') AS jsondata FROM pr_models WHERE model_id = 2 SELECT JSON_QUERY(model_json,'$.nodeDataArray..category') AS jsondata FROM pr_models WHERE model_id = 2 result NULL.

CodePudding user response：

You want:

JSON_EXTRACT(model_json,'$.nodeDataArray[*].category') AS jsondata

fiddle