mysql json_extract用法,如何使用mysql udf json_extract 0.4.0从json数组中提取行？

I have some sql that I want to pass into a mysql stored procedure. I'm using the json functions in mysql-json-udfs-0.4.0-labs-json-udfs-linux-glibc2.5-x86_64. We are running a mysql 5.5.4 server. Updating to 5.7.x is an option.

When I run

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` ;

I am expecting

from to

12 0

11 -1

1 1

I am getting

from to

{"from":12,"to":0} {"from":12,"to":0}

The question is how to extract rows from a json array using the udf json_extract 0.4.0?

I solved this for the moment by using comma_schema with json as

{

"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

}

]

}

which gives the result after running

select `common_schema`.`extract_json_value`(@mapJSON,'/map/from') `from`,`common_schema`.`extract_json_value`(@mapJSON,'/map/to') `to` ;

as space delimited strings

from to

12 1 2 3 4 5 6 7 8 9 10 0 10 20 30 40 50 60 70 80 90 100

which I then extract using where @recommendationMapJSON is the new json being passed into the stored procedure.

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` = '' ;

which gives the map that I wanted.

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

解决方案

Your syntax inside json_extract() was a bit off. Try using this instead:

SET @mapJSON = '[{"from":12,"to":0},{"from":11,"to":-1},{"from":1,"to":1}]' ;

SELECT JSON_EXTRACT(@mapJSON, "$.from") AS `from`,

JSON_EXTRACT(@mapJSON, "$.to") AS `to`

This should give you a result set looking something like this:

from to

[12, 11, 1] [0, -1, 1]