Fetch multiple values stored as key-value pairs in the json date type and display as columns

Question

I have a database table that stores the sales data and the associated extra charges in a key-value format using the json date type. Following is the minimal structure.

saleID  amount extra_charges
------- ------ ----------------------------------------------------------
123     1000   [{"key": "Handling Charges", "amount": 20}, {"key": "Packing Charges", "amount": 15}]
345     1500   [{"key": "Transportation Charges", "amount": 10}, {"key": "Packing Charges", "amount": 0}]
567     240    [{"key": "Handling Charges", "amount": 10}, {"key": "Transportation Charges", "amount": 20}, {"key": "Packing Charges", "amount": 15}]
...

I would like to display the data in the following format in my dashboard:

Sale ID  Amount  Handling Charges   Transportation Charges   Packing Charges
-------  ------  -----------------  -----------------------  ----------------
123      1000    20                 0                        15
345      1500    0                  10                       0
567      240     10                 20                       15
...

I have tried using json_extract function to fetch the data, but failed.

json_extract(extra_charges, '$.key')

How would I write a query for this?

Once you have parsed the data, build an RDBMS table with the columns. JSON is clumsy to work with. — Rick James, Aug 09 '23 at 20:56

Clara · Answer 1 · 2023-08-08T10:15:29.327

It was hard, but I found a solution.

As you cannot change the structure of the data stored, a possible solution could be something like this (replace yourTable with the right name):

SELECT 
    yourTable.saleID AS 'Sale ID', 
    yourTable.amount as 'Amount',
    IFNULL(
        JSON_EXTRACT(newJSON, '$.Handling_Charges'), 0
    ) AS 'Handling Charges',

    IFNULL(
        JSON_EXTRACT(newJSON, '$.Packing_Charges'), 0
    ) AS 'Packing Charges',

    IFNULL(
        JSON_EXTRACT(newJSON, '$.Transportation_Charges'), 0
    ) AS 'Transportation Charges'

FROM yourTable, 
(
    SELECT saleID, CONCAT('{', GROUP_CONCAT(jsonText SEPARATOR ','), '}') AS newJSON
    FROM (
        SELECT saleID,
            CONCAT('"', REPLACE(keyVal, ' ', '_'), '"', ': ',
            IF(JSON_EXTRACT(extra_charges, CONCAT('$[0].key')) = keyVal,
                JSON_EXTRACT(
                    extra_charges, 
                    CONCAT('$[0].amount')
                ), 
                IF(JSON_EXTRACT(extra_charges, CONCAT('$[1].key')) = keyVal,
                    JSON_EXTRACT(
                        extra_charges, 
                        CONCAT('$[1].amount')
                    ), 
                    IF(JSON_EXTRACT(extra_charges, CONCAT('$[2].key')) = keyVal,
                        JSON_EXTRACT(
                            extra_charges, 
                            CONCAT('$[2].amount')
                        ), 
                        0
                    )
                )
            )) AS jsonText
        FROM spr_lead
        JOIN ( 
          SELECT 'Handling Charges' AS keyVal UNION
          SELECT 'Transportation Charges' AS keyVal UNION
          SELECT 'Packing Charges' AS keyVal 
        ) AS `keys` 
    ) AS auxTable
    GROUP BY saleID
) AS newJson
WHERE newJson.saleID = yourTable.saleID

This query is composed by three nested queries that are explained below:

The innermost query

SELECT 
    saleID,
    CONCAT('"', REPLACE(keyVal, ' ', '_'), '"', ': ',
        IF(JSON_EXTRACT(extra_charges, CONCAT('$[0].key')) = keyVal,
            JSON_EXTRACT(
                extra_charges, 
                CONCAT('$[0].amount')
            ), 
            IF(JSON_EXTRACT(extra_charges, CONCAT('$[1].key')) = keyVal,
                JSON_EXTRACT(
                    extra_charges, 
                    CONCAT('$[1].amount')
                ), 
                IF(JSON_EXTRACT(extra_charges, CONCAT('$[2].key')) = keyVal,
                    JSON_EXTRACT(
                        extra_charges, 
                        CONCAT('$[2].amount')
                    ), 
                    0
                )
            )
        )) AS jsonText
    FROM spr_lead
    JOIN ( 
      SELECT 'Handling Charges' AS keyVal UNION
      SELECT 'Transportation Charges' AS keyVal UNION
      SELECT 'Packing Charges' AS keyVal 
    ) AS `keys`
) AS auxTable

It gets the JSON field and creates a string with structure "key": value replacing the spaces in the key with a '_'. It will return something like this:

saleID	jsonText
123	'"Handling_Charges": 20'
123	'"Transportation_Charges": 0'
123	'"Packing_Charges": 15'
345	'"Handling_Charges": 0'
345	'"Transportation_Charges": 10'
345	'"Packing_Charges": 0'
...	...

The middle query

This query joins all the strings created by the innermost query to create a new JSON string grouping them by ID:

SELECT saleID, CONCAT('{', GROUP_CONCAT(jsonText SEPARATOR ','), '}') AS newJSON
    FROM (
        ... #the innermost query
    ) AS auxTable
    GROUP BY saleID
) AS newJson

It returns something like this:

saleID	newJSON
123	'{"Handling_Charges": 0,"Transportation_Charges": 0,"Packing_Charges": 0}'
345	'{"Handling_Charges": 0,"Transportation_Charges": 10,"Packing_Charges": 0}'
...	...

The outter query

And finally, the outter query extracts the JSON data using JSON_EXTRACT and returns the expected output.

Thanks for the reply, Clara. I'm afraid I'm not in a position to change the structure of the data stored in the table. I could fetch it easily if the data format were like this. — Hareesh Sivasubramanian, Aug 03 '23 at 12:03

Solarflare · Answer 2 · 2023-08-08T20:23:20.603

With MySQL 8, you can make use of JSON_TABLE():

SELECT mytable.saleID, mytable.amount, hc.amount AS handling_charges,
  tc.amount AS transportation_charges, pc.amount AS packing_charges
FROM mytable 
LEFT JOIN json_table(mytable.extra_charges, 
  '$[*]' COLUMNS(keyname text path '$.key', amount float path '$.amount')
) AS hc ON hc.keyname = 'Handling Charges'
LEFT JOIN json_table(mytable.extra_charges, 
  '$[*]' COLUMNS(keyname text path '$.key', amount float path '$.amount')
) AS tc ON tc.keyname = 'Transportation Charges'
LEFT JOIN json_table(mytable.extra_charges, 
  '$[*]' COLUMNS(keyname text path '$.key', amount float path '$.amount')
) AS pc ON pc.keyname = 'Packing Charges'

The part

json_table(mytable.extra_charges, 
  '$[*]' COLUMNS(keyname text path '$.key', amount float path '$.amount'))

creates, for each row of mytable, a derived table with the columns (keyname, amount) and a row for each json-key (e.g. in your case up to 3 rows). Out of these rows, we pick, in the ON-clause, the row with the correct value (e.g. with ON pc.keyname = 'Packing Charges' we select the row with the value 'Packing Charges'), and do this left join 3 times for each charge type (to generate the 3 output columns).

Both your data format (json) and the output format (you want a pivot, e.g. what usually would be a row becomes a column) complicates your query, but this is probably a relatively compact solution for your requirements.

Note that you have to modify the query if you e.g. add a new charge type (then you have to add a new output column/left join), which is typical for pivot queries.

score 0 · Accepted Answer · answered Aug 10 '23 at 11:00

I used JSON_EXTRACT recursively to fetch the key from the extra_charges. This way, I didn't have to create a temp derived table as suggested in the JSON_TABLE approach.

Further, this method works in MySQL 5.7 version as well.

SELECT
 COALESCE(JSON_EXTRACT(JSON_EXTRACT(extra_charges, JSON_UNQUOTE(JSON_SEARCH(JSON_EXTRACT(extra_charges, '$[*].key'), 'one', 'Handling Charges'))), '$.amount'), 0) as 'Handling Charges',
 COALESCE(JSON_EXTRACT(JSON_EXTRACT(extra_charges, JSON_UNQUOTE(JSON_SEARCH(JSON_EXTRACT(extra_charges, '$[*].key'), 'one', 'Transportation Charges'))), '$.amount'), 0) as 'Transportation Charges',
 COALESCE(JSON_EXTRACT(JSON_EXTRACT(extra_charges, JSON_UNQUOTE(JSON_SEARCH(JSON_EXTRACT(extra_charges, '$[*].key'), 'one', 'Packing Charges'))), '$.amount'), 0) as 'Packing Charges'
FROM mytable

Fetch multiple values stored as key-value pairs in the json date type and display as columns

3 Answers3