How to write a MySQL function to extract JSONAttribute's value, based on name?

Question

I have a requirement that I need to write a mysql function which takes 'JSONText', 'color' as parameters and output based on the 'color'

[ 
  {
    "color":"red",
    "points":4
  },
  {
    "color": "Electric Blue",
    "points": 5
  }
 ]

So, my function would be

 DELIMITER $$
 CREATE FUNCTION GetColorPoints(JSONParam Text, color VARCHAR(10)) RETURNS INT
 BEGIN
        **???** //WHAT SHOULD GO HERE??
 END$$
 DELIMITER;

So, if I call the function with inputs, it should give me points.

 SELECT GetColorPoints('[ {"color":"red", "points":4}, {"color": "Electric Blue", "points": 5} ]', 'Electric Blue') AS 'ColorPoints';

This is not a valid json object, are you sure the first and last curly brace "{" and "}" aren't suppose to be square brackets "[" and "]" ? — ggordon, Mar 30 '20 at 21:23
You are right @ggordon, I will correct the same in the question. thank you ! — Srinivas Lakshman, Mar 30 '20 at 21:33

score 2 · Answer 1 · answered Mar 30 '20 at 22:21

2

If you are happy enough to use MySQL 8.0, you can do this with JSON_TABLE():

set @js = '[ { "color":"red", "points":4 }, { "color": "Electric Blue", "points": 5 } ]';
select points
from json_table(
    @js,
    '$[*]' columns(
        color varchar(50) path '$.color',
        points int path '$.points'
    )
) t
where color = 'red'

answered Mar 30 '20 at 22:21

GMB

216,147
25
84
135

Hi @GMB. Thanks for reply. I'm currently using 5.7 version of mysql – Srinivas Lakshman Mar 31 '20 at 14:55

How to write a MySQL function to extract JSONAttribute's value, based on name?

1 Answers1