Is there a way to get the max of a JSON array in MYSQL

Question

For reasons I can't control, I need to parse a json column in MYSQL for all of the instances of a key and then get the max of those. Let's say the column name is json_data and the json looks like this:

    {
        list_of_stuff: {
            {
                value_i_want: 1
            },
            {
                value_i_want: 2
            },
            {
                value_i_want: 4
            },
            {
                value_i_want: 3
            }
        }
    }

Where I would need to get the maximum of the value_i_want tag, which in this case would be 4.

Right now I have the following code:

SELECT JSON_EXTRACT(json_data->"$.list_of_stuff", "$**.value_i_want") from table_name;

But it is returning JSON arrays, and when I try to get the maximum of those arrays, it is just returning the row with the longest json array, not the maximum value of each of those arrays.

Thanks for your help!

EDIT My MYSQL version is 5.7.25

utsav-bosleo · Accepted Answer · 2019-08-14 07:07:18Z

your json is not valid as per json validator so i am considering below valid json :

{
  "list_of_stuff": [
    {
      "value_i_want": 1
    },
    {
      "value_i_want": 2
    },
    {
      "value_i_want": 4
    },
    {
      "value_i_want": 3
    }
  ]
}

this can be work with mysql 5.7 :

create mysql function for returning max value and then you can use this function with any select query. see example

create table Test(id integer, name json);
insert into Test(id, name) values(1, '{
  "list_of_stuff": [
    {
      "value_i_want": 1
    },
    {
      "value_i_want": 2
    },
    {
      "value_i_want": 4
    },
    {
      "value_i_want": 10
    }
  ]
}');

-- Your code here!

DELIMITER //

CREATE FUNCTION getMax(s JSON) RETURNS CHAR(50) DETERMINISTIC
    BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE max INT;
    DECLARE temp INT;
    SET max = JSON_EXTRACT(s, '$[0]');
    WHILE i < JSON_LENGTH(s) DO
        SET temp = JSON_EXTRACT(s, CONCAT('$[',i,']'));
        IF max < temp THEN
            SET max = temp;
        END IF;
        SET i = i + 1;
    END WHILE;
    RETURN max;
    END
//
DELIMITER ;

SELECT getMax(JSON_EXTRACT(name->"$.list_of_stuff", "$**.value_i_want")) FROM Test;

utsav-bosleo · Accepted Answer · 2019-08-13 13:20:04Z

0

MYSQL version 8.0 possible by creating temporary json_table and then find max().

DB fiddle create table Test(id integer, name json);

insert into Test(id, name) values(1, '{
  "list_of_stuff": [
    {
      "value_i_want": 1
    },
    {
      "value_i_want": 2
    },
    {
      "value_i_want": 4
    },
    {
      "value_i_want": 3
    }
  ]
}');

SELECT @json_var := cast(JSON_EXTRACT(name->"$.list_of_stuff", "$[*]") as char(200))  from Test;
SELECT max(tt.ac)
     FROM
      JSON_TABLE(
        @json_var,
       "$[*]"
        COLUMNS(
           rowid FOR ORDINALITY,
          ac int PATH "$.value_i_want" 

         )
  ) AS tt;

edited Aug 13, 2019 at 13:20

answered Aug 13, 2019 at 13:10

utsav-bosleo

313 bronze badges

1 Comment

Solomon Park Over a year ago

Is there any way to do this without MYSQL 8? I am on 5.7.25

Collectives™ on Stack Overflow

Is there a way to get the max of a JSON array in MYSQL

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related