By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 10.9.2-MariaDB-1:10.9.2+maria~deb11 |
create table pr_models (model_id int, model_name text, model_json blob);
insert into pr_models values (2,'Foo','{"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]}
]}');
SELECT
JSON_QUERY(model_json,'$.nodeDataArray') AS jsondata
FROM pr_models
WHERE model_id = 2
| jsondata |
|---|
| [ {"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"} ] |
SELECT
JSON_EXTRACT(model_json,'$.nodeDataArray[*].category') AS jsondata
FROM pr_models
WHERE model_id = 2
| jsondata |
|---|
| ["Start", "Step", "Step", "Step", "End", "Conditional", "Step"] |