How do I return MariaDB records looking in a JSON path-value pair?

Viewed 23

I'm building a website based on OpenCart v.3.0.3.2 framework that runs on a XAMPP v.7.4.1 for Windows 10 x64 which includes PHP v.7.4.1 and MariaDB v.10.4.11. On user_group Table at permission Column, OpenCart stores the data in Json format. Now, I made a query, that can search through the records and Json array based on path key-value pair.

SET @search_value = "catalog\/attribute";
SET @search_path = "$.access";

SELECT 
    * 
FROM
    DB_PREFIX_user_group 
WHERE 
    JSON_UNQUOTE        #returns: catalog/attribute
    (
        JSON_EXTRACT        #returns: "catalog\/attribute"
        (
            JSON_EXTRACT        #returns: ["catalog\/attribute", "catalog\/attribute_group",...
            (
                permission, 
                @search_path
            ), 
            JSON_UNQUOTE
            (
                JSON_SEARCH        #returns: "$[0]"
                (
                    JSON_EXTRACT        #returns: ["catalog\/attribute", "catalog\/attribute_group",...
                    (
                        permission, 
                        @search_path
                    ),
                    'one', 
                    @search_value
                )
            )
        )
    ) = @search_value;

Is there any other way to find if a record has the specific path key-value pair inside Json Array?

0 Answers
Related