我有一些要传递到mysql存储过程中的SQL.我正在使用mysql-json-udfs-0.4.0-labs-json-udfs-linux-glibc2.5-x86_64中的json函数.我们正在运行mysql 5.5.4服务器.可以选择更新到5.7.x.
当我跑步
set @mapJSON = '[{"from":12,"to":0},{"from":11,"to":-1},{"from":1,"to":1}]' ;
select json_extract(@mapJSON,'from') `from`,json_extract(@mapJSON,'to') `to` ;
我期待
from to
12 0
11 -1
1 1
我正进入(状态
from to
{"from":12,"to":0} {"from":12,"to":0}
问题是如何使用udf json_extract 0.4.0从json数组提取行?
我暂时通过将comma_schema与json一起使用来解决了这个问题
{
"map": [
{
"from": 12,
"to": 0
},
{
"from": 1,
"to": 10
},
{
"from": 2,
"to": 20
},
{
"from": 3,
"to": 30
},
{
"from": 4,
"to": 40
},
{
"from": 5,
"to": 50
},
{
"from": 6,
"to": 60
},
{
"from": 7,
"to": 70
},
{
"from": 8,
"to": 80
},
{
"from": 9,
"to": 90
},
{
"from": 10,
"to": 100
}
]
}
运行后给出结果
select `common_schema`.`extract_json_value`(@mapJSON,'/map/from') `from`,`common_schema`.`extract_json_value`(@mapJSON,'/map/to') `to` ;
作为空格分隔的字符串
from to
12 1 2 3 4 5 6 7 8 9 10 0 10 20 30 40 50 60 70 80 90 100
然后我使用@recommendationMapJSON提取其中的内容,即将新的json传递到存储过程中.
create temporary table temporary_recommendation_maps AS (
select `common_schema`.`extract_json_value`(@recommendationMapJSON,'/map/from') `from`,`common_schema`.`extract_json_value`(@recommendationMapJSON,'/map/to') `to`
) ;
create temporary table temporary_recommendation_map (
`from` int ,
`to` int
) ;
select length(`from`) - length(replace(`from`,' ','')) +1 into @mapCount from temporary_recommendation_maps ;
set @mapIndex = 0 ;
while @mapIndex < @mapCount do
select substring_index(`from`,' ',1) into @from from temporary_recommendation_maps ;
select substring_index(`to`,' ',1) into @to from temporary_recommendation_maps ;
insert into temporary_recommendation_map(`from`,`to`) values (@from,@to) ;
update temporary_recommendation_maps
set `from` = substring(`from`,instr(`from`,' ')+1)
, `to` = substring(`to`,instr(`to`,' ')+1) ;
set @mapIndex = @mapIndex + 1 ;
end while ;
update temporary_recommendation_maps
set `from` = ''
, `to` = '' ;
给出了我想要的地图.
select * from temporary_recommendation_map ;
from to
12 0
1 10
2 20
3 30
4 40
5 50
6 60
7 70
8 80
9 90
10 100
解决方法:
使用索引获取数组值.
$[ index ]
样品:
SELECT JSON_EXTRACT(@mapJSON, "$[0].from") AS 'from',
JSON_EXTRACT(@mapJSON, "$[0].to") AS 'to' ;