MYSQL JSON Function-CodePudding

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.

CodePudding user response：

WHILE iterator > 0 DO

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

CodePudding user response：

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