MYSQL JSON Function

Question

This is the first function I've ever done in MySQL, so I'm in trouble about how to make it do what I want. The function is syntactically correct, but it doesn't work.

The goal of this function should be to delete, from a JSON object, all the items containing a key named "checked", as the following example:

{
    "type":{
        "checked":"True", <---
        "editable":"0",
        "data_type":"string",
        "value_name":"type",
        "value_type":"-",
        "value_unit":"-",
        "raw_attribute":{
        },
        "healthiness_value":"300",
        "healthiness_criteria":"refresh_rate"
    },
    "category":{
        "checked":"True", <---
        "editable":"0",
        "data_type":"string",
        "value_name":"category",
        "value_type":"-",
        "value_unit":"",
        "raw_attribute":{
        },
        "healthiness_value":"300",
        "healthiness_criteria":"refresh_rate"
    },
    "occupier":{
        "checked":"False", <---
        "editable":"0",
        "data_type":"string",
        "value_name":"occupier",
        "value_type":"-",
        "value_unit":"",
        "raw_attribute":{
    },
    ...
    }
}

So, what I want is to have a function that filter, and show me when I do a query, only attributes that are checked "True" or "False", in consequence of the attribute checked given in the following SQL function:

This is saved inside a tuple of a MySQL db, under the column "attribute".

CREATE FUNCTION delete_checked(attributes JSON, checked VARCHAR(7)) 
RETURNS JSON 
BEGIN 
DECLARE attributes_keys JSON; 
DECLARE iterator INT; 
DECLARE attribute VARCHAR(30); 
DECLARE _attributes JSON; 
DECLARE _path VARCHAR(100);
SET attributes_keys = JSON_KEYS(attributes); 
SET _attributes = attributes; 
WHILE iterator > 0 DO
SET attribute = JSON_EXTRACT(attributes_keys, CONCAT("$[", iterator, "]"));
SET _path = CONCAT('$.', attribute, '.checked');
IF JSON_CONTAINS(_attributes, checked, _path) THEN 
SET _attributes = JSON_REMOVE(attributes, CONCAT('$.', attribute)); 
END IF; 
SET iterator = iterator - 1; 
END WHILE; RETURN _attributes; 
END//

Use case: "SELECT delete_checked(attributes, "False") FROM table WHERE ...". Otherwise, I should make this by filtering this out of db, but I don't like this way.

Like many questions about using JSON in MySQL, this would be an elementary problem if you did not use JSON, but instead stored your entries one per row. Then you could DELETE FROM MyTable WHERE checked = TRUE. So why did you decide to use a single JSON document for these data instead of normal rows and columns? — Bill Karwin
– Bill Karwin, Commented Mar 25, 2022 at 0:07

E-telier · Accepted Answer · 2022-03-25 08:07:06Z

1

WHILE iterator > 0 DO

your loop won't start since iterator is only declared but not set.

answered Mar 25, 2022 at 8:07

E-telier

8216 silver badges17 bronze badges

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

1 Comment

Giuseppe Over a year ago

Mh i corrected it but still don't work

Akina · Accepted Answer · 2022-03-25 10:28:45Z

0

CREATE FUNCTION my_function (json_doc JSON, checked VARCHAR(5))
RETURNS TEXT
RETURN ( SELECT JSON_OBJECTAGG(one_key, output)
         FROM ( SELECT one_key, JSON_EXTRACT(json_doc, CONCAT('$.', one_key)) output
                FROM JSON_TABLE(JSON_KEYS(json_doc),
                                '$[*]' COLUMNS (one_key VARCHAR(64) PATH '$')) jsontable
                HAVING output->>'$.checked' = checked ) subquery );

USAGE: SELECT ..., my_function(table.json_column, 'True | False'), ...

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=71943335ae59d9836e6309e2a3a380ab

answered Mar 25, 2022 at 10:28

Akina

43.2k6 gold badges17 silver badges30 bronze badges

2 Comments

Giuseppe Over a year ago

Thanks! It works, but only on single row queries.

Akina Over a year ago

@Giuseppe but only on single row queries. O_O if so then you did something wrongly.

Collectives™ on Stack Overflow

MYSQL JSON Function

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related