1

I'm stuck with MySQL 5.7 and to reduce the amount of data retrieved I need to transform a JSON array within a SQL query.

My column is JSON and with a value structured like that:

{
  "foo": [{
    "bar" : "BAR1",
    "baz" : "BAZ1",
    "qux" : "QUX1"
  }, {
    "bar" : "BAR2",
    "baz" : "BAZ2",
    "qux" : "QUX2"
  }]
}

I don't know how many items are in the array. I want the result to be structured like that:

{
  "foo" : [
    ["BAR1", "BAZ1"],
    ["BAR2", "BAZ2"]
  ]
}

Basically I need to transform item objects to arrays (and skip qux).

Unfortunately JSON_REMOVE does not allow wildcard:

An error occurs if [...] any path argument is not a valid path expression or contains a * or ** wildcard.

Is it possible to pass JSON_SEARCH results as JSON_ARRAY or JSON_REMOVE arguments?

Yves M.
  • 29,855
  • 23
  • 108
  • 144
  • Do you have to do it in MySQL? A script in Python or PHP could do this more easily. – Barmar Dec 15 '22 at 15:44
  • This will be pretty difficult in 5.7. In 8.0 you could use `JSON_TABLE()` and `JSON_ARRAYAGG()` to transform it. If you can't do that, you'll need to write a stored procedure with a loop. – Barmar Dec 15 '22 at 15:45
  • I need to filter out data before getting it from DB (I have AWS Aurora Serverless restrictions about maximum amount if retrieved data ‍♂️) – Yves M. Dec 15 '22 at 15:48
  • Then it will have to be a loop in a stored procedure. – Barmar Dec 15 '22 at 15:50

0 Answers0