MySQL: JSON to Resultset

Question

I have an archive table that has a column which contains unindexed json records. Sample:

[{
    "attr1": "val1",
    "attr2": "val2",
    "attr3": "val3",
    .
    .
    "attrN": "valN"
}, {
    "attr1": "val1.2",
    "attr2": "val2.2",
    "attr3": "val3.2",
    .
    .
    "attrN": "valN.2"
},...]

I need to create a stored procedure or function that returns the json as a resultset:

  attr1   |   attr2    |   attr3  | ... |  attrN
  _______________________________________________
  val1    |    val2    |   val3   | ... |  valN
  val1.2  |    val2.2  |   val2.2 | ... |  valN.2

I need it to return as a recordset as I will be using it for other queries in other stored procedures.

I was able to achieve this with this: Read JSON array in MYSQL

However, I was wondering if there is another way besides creating a temporary table? I was thinking about performance and efficiency. Like, what if 20 or 50 users trigger this? Is there a better way to do this?

Note: the avarage size of the json is ~1MB

MySQL: JSON to Resultset

1 Answers