How do I retrieve a unique list of JSON values from JSON arrays stored in TEXT columns in multiple r-CodePudding

I currently have an array of JSON objects stored in a TEXT column called 'tax_components' in a table called 'orders' in a MariaDB Database (version 10.4) like below:

Row 1

[
{"tax_type":"Vat 15","tax_percentage":"15.00","tax_amount":"13.04"},
{"tax_type":"Tobacco","tax_percentage":"100.00","tax_amount":"50.00"},
{"tax_type":"Exempt","tax_percentage":"0.00","tax_amount":"0.00"},
{"tax_type":"Zero","tax_percentage":"0.00","tax_amount":"0.00"}
]

Row 2

[
{"tax_type":"Vat","tax_percentage":"15.00","tax_amount":"52.17"},
{"tax_type":"Exempt","tax_percentage":"0.00","tax_amount":"0.00"},
{"tax_type":"Tobacco Tax","tax_percentage":"0.00","tax_amount":"0.00"}
]

I have multiple rows with values similar to the above in the table.

The above JSON array has different values in different rows. Sometimes it has tax_type = 'Tobacco', sometimes it has tax_type = "Tobacco Tax", sometimes it doesn't.

Could someone please tell me what SQL query to write to extract a unique list of all the tax_type values and corresponding tax_percentages stored in all the JSON arrays in all the rows FROM this 'tax_components' column ?

Thanks in advance.

CodePudding user response：

WITH RECURSIVE
cte AS (
    SELECT id, val, 1 element, 
           JSON_UNQUOTE(JSON_EXTRACT(val, '$[0].tax_type')) tax_type, 
           JSON_UNQUOTE(JSON_EXTRACT(val, '$[0].tax_percentage')) tax_percentage, 
           JSON_UNQUOTE(JSON_EXTRACT(val, '$[0].tax_amount')) tax_amount
    FROM test
    UNION ALL
    SELECT id, val, 1   element, 
           JSON_UNQUOTE(JSON_EXTRACT(val, CONCAT('$[', element, '].tax_type'))), 
           JSON_UNQUOTE(JSON_EXTRACT(val, CONCAT('$[', element, '].tax_percentage'))), 
           JSON_UNQUOTE(JSON_EXTRACT(val, CONCAT('$[', element, '].tax_amount')))
    FROM cte
    WHERE element < JSON_LENGTH(val)
)
SELECT id, tax_type, tax_percentage, tax_amount 
FROM cte

If you need in unique values for some definite JSON attribute then remove unneeded attributes expressions from CTE, remove id and add DISTINCT to outer query.

https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=c1baff9748ab9be320052a73258951c8