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?