How do I retrieve a unique list of JSON values from JSON arrays stored in TEXT columns in multiple rows in a MariaDB table

Question

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.

Akina · Accepted Answer · 2022-06-27T08:54:46.537

0

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

edited Jun 27 '22 at 08:54

answered Jun 27 '22 at 08:42

Akina

39,301
5
14
25

WITH RECURSIVE doesn't work in my MySQL version. And I don't need tax_amount. I only need the unique tax_type and corresponding tax_percentage – Anand George Jun 27 '22 at 08:57
@AnandGeorge You type "MariaDB Database (version 10.4)". The fiddle uses MariaDB 10.4. MariaDB supports recursive CTE since 10.2. – Akina Jun 27 '22 at 08:58
@AnandGeorge *I only need the unique tax_type and corresponding tax_percentage* Either unique or according. Also unique pairs (tax_type, tax_percentage) is an option. – Akina Jun 27 '22 at 08:59
Can you please suggest a solution for MySQL version 7.4. I get the following error when I use WITH RECURSIVE Unrecognized statement type. (near "WITH" at position 0) . And don't I need to mention the table name in the query? – Anand George Jun 27 '22 at 09:36
@AnandGeorge *MySQL version 7.4* There is no such version... 5.x then 8.x. What is complete output for `SELECT VERSION();`? – Akina Jun 27 '22 at 09:40
10.4.16-MariaDB – Anand George Jun 27 '22 at 09:43
Can you please suggest a solution for MySQL version 5.x. that doesn't use WITH RECURSIVE I get the following error when I use WITH RECURSIVE Unrecognized statement type. (near "WITH" at position 0). – Anand George Jun 27 '22 at 09:48
@AnandGeorge *10.4.16-MariaDB* It supports recursive CTEs. If you query via PHP/phpMyAdmin then it is possible that you use old version which does not know about CTEs but tries to validate the query and generates an error. *a solution for MySQL version 5.x* This is unsolvable on 5.x in single query format until some predefined limit of objects per JSON array exists (for example, not more than 10 objects in one JSON). Without such limit the only option is iterative stored procedure. – Akina Jun 27 '22 at 10:02
It's working now. Thank you so much. Sorry I'm unable to upvote your answer as I don't have enough points. – Anand George Jun 27 '22 at 11:44

How do I retrieve a unique list of JSON values from JSON arrays stored in TEXT columns in multiple rows in a MariaDB table

1 Answers1