How can I retrieve a specific JSON value from a JSON array containing a list of JSON objects stored-CodePudding

I currently have an array of JSON objects stored in a TEXT column called 'tax_components' in a table called 'orders' in a MySQL Database (Version 7.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 15","tax_percentage":"15.00","tax_amount":"52.17"},

{"tax_type":"Exempt ","tax_percentage":"0.00","tax_amount":"0.00"},

{"tax_type":"Zero ","tax_percentage":"0.00","tax_amount":"0.00"}

]

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

I would like to do the same for all the tax amount values corresponding to 'tax_type' = 'Vat 15', 'tax_type' = 'Tobacco', 'tax_type' = 'Exempt' and 'tax_type' = 'Zero'

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

Could someone please tell me what SQL query to write to extract and sum all the 'tax_amount' values corresponding to the different 'tax_type' in this 'tax_components' column ?

Thanks in advance.

CodePudding user response：

This works: (adapted from another answer by @Barmar)

SELECT
    SUM(
        CAST(
            (
                JSON_EXTRACT (
                    `tax_components`,
                    JSON_UNQUOTE(
                        REPLACE(
                            JSON_SEARCH(`tax_components`, 'one', 'Vat 15'),
                            'tax_type',
                            'tax_amount'
                        )
                    )
                )
            ) AS DECIMAL(10, 2)
        )
    )
FROM
    orders