How to extract rows from a json array using the mysql udf json_extract 0.4.0?

Question

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

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

Vishal_Kotecha · Accepted Answer · 2022-07-21 07:31:37Z

6

Bit late to the party but here's how I did it.

Example:

select json_extract(@mapJSON,'$[*].from') `from`,json_extract(@mapJSON,'$[*].to') `to`

answered Jul 21, 2022 at 7:31

Vishal_Kotecha

4906 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tom Aranda · Accepted Answer · 2017-12-15 21:54:38Z

5

Use index to get array value.

$[ index ]

sample:

SELECT JSON_EXTRACT(@mapJSON, "$[0].from") AS 'from',
         JSON_EXTRACT(@mapJSON, "$[0].to") AS 'to' ;

edited Dec 15, 2017 at 21:54

Tom Aranda

6,03511 gold badges39 silver badges53 bronze badges

answered Dec 15, 2017 at 21:49

iSoft Sistemas

811 silver badge4 bronze badges

1 Comment

Keith John Hutchison Over a year ago

That results in from to {"from":12,"to":0} {"from":12,"to":0} on my server

iSoft Sistemas · Accepted Answer · 2017-12-19 21:53:47Z

3

try this code.

DROP TABLE IF EXISTS tmp;
DROP PROCEDURE IF EXISTS teste;

DELIMITER $$

CREATE PROCEDURE teste()
BEGIN

  DECLARE i      INT DEFAULT  0;
  DECLARE jCount INT DEFAULT -1;

  CREATE TEMPORARY TABLE tmp( ou_from INT, out_to INT );

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

  SET jCount = jCount + JSON_LENGTH( @mapJSON, '$' );

  WHILE ( i <= jCount ) DO

    INSERT INTO tmp( ou_from, out_to ) 
    VALUES( JSON_EXTRACT(@mapJSON, CONCAT( '$[', i, '].from') )
          , JSON_EXTRACT(@mapJSON, CONCAT( '$[', i, '].to'  ) )
          );
    SET i = i + 1;

  END WHILE;

  SELECT ou_from AS 'from', out_to AS 'to' FROM tmp;

  /*
  SELECT JSON_EXTRACT(@mapJSON, "$[1].from") AS 'from',
         JSON_EXTRACT(@mapJSON, "$[1].to") AS 'to' ;
*/

END $$

DELIMITER ;

CALL teste;

answered Dec 19, 2017 at 21:53

iSoft Sistemas

811 silver badge4 bronze badges

1 Comment

iSoft Sistemas Over a year ago

I make sample in mySql 5.7

Keith John Hutchison · Accepted Answer · 2017-12-16 00:18:13Z

2

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]

edited Dec 16, 2017 at 0:18

Keith John Hutchison

5,34711 gold badges49 silver badges66 bronze badges

answered Apr 1, 2016 at 1:15

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

2 Comments

Keith John Hutchison Over a year ago

I'm getting from to {"from":12,"to":0} {"from":12,"to":0} with the example.

w. Patrick Gale Over a year ago

Not sure what version of MySQL this applies to but in MySQL 8 I get no results.

Collectives™ on Stack Overflow

How to extract rows from a json array using the mysql udf json_extract 0.4.0?

4 Answers 4

Comments

1 Comment

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related