Mysql Question posted in
The official MySQL documentation can be found here.

MySQL change JSON value from type 'OBJECT' to 'ARRAY'

Help! I have a MySQL table with a JSON column in it. A while back I pushed a change that filtered a PHP array but without resetting the array keys, so that when it got converted into JSON the array got stored as an object. I have a I need to convert this e.g.:

{
  "recipients": {
    "1": {
      "name": "Alice", 
      "email": "[email protected]"
    }, 
    "2": {
      "name": "Bob",
      "email": "[email protected]"
    }
  },
  // ...
}

to


{
  "recipients": [
    {
      "name": "Alice", 
      "email": "[email protected]"
    }, 
    {
      "name": "Bob",
      "email": "[email protected]"
    }
  ],
  // ...
}

I’d like to do this conversion in-query if possible. I can isolate the errant rows with

SELECT data 
FROM table1 
WHERE JSON_TYPE(JSON_EXTRACT(data, '$.recipients')) = 'OBJECT'

but am stuck on doing the conversion and updating the data without affecting other keys in the JSON. Can this be done in-query or do I have to do the conversion in PHP?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    All credit to Bill's answer for doing the heavy lifting. I only had to make one adjustment - adding a GROUP BY so the aggregation did not combine all rows.

    I could then use this as a subquery joined back to the main table. Since I am using JSON_SET to set the value at the 'recipients' key, the subquery can just return the transformed value without needing JSON_OBJECT (or JSON_PRETTY).

    Here's the final query:

    UPDATE table1
JOIN (
    SELECT
        entry_id, -- for joining back to the main table
        JSON_ARRAYAGG(JSON_EXTRACT(data, CONCAT('$.recipients."', k.recipients_key, '"'))) AS recipients
    FROM table1
    CROSS JOIN JSON_TABLE(JSON_KEYS(data, '$.recipients'), '$[*]' COLUMNS (recipients_key INT PATH '$')) k
    WHERE JSON_TYPE(JSON_EXTRACT(data, '$.recipients')) = 'OBJECT'
    GROUP BY entry_id -- important or it will combine all rows into a single result!
) AS fixed
ON fixed.entry_id = table1.entry_id
SET data = JSON_SET(data, '$.recipients', fixed.recipients)
WHERE JSON_TYPE(JSON_EXTRACT(data, '$.routed_to')) = 'OBJECT'

  2. 0 
    SELECT JSON_PRETTY(
  JSON_OBJECT(
    "recipients", JSON_ARRAYAGG(
      JSON_EXTRACT(data, CONCAT('$.recipients."', k.rkey, '"'))
    )
  ) 
) AS _newjson
FROM table1
CROSS JOIN JSON_TABLE(JSON_KEYS(data, '$.recipients'), '$[*]' COLUMNS (rkey INT PATH '$')) k
WHERE JSON_TYPE(JSON_EXTRACT(data, '$.recipients')) = 'OBJECT';

    Output given your test data:

    {
  "recipients": [
    {
      "name": "Alice",
      "email": "[email protected]"
    },
    {
      "name": "Bob",
      "email": "[email protected]"
    }
  ]
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search