Query on JSON data type. MySQL.

Excuse me.. I'm still learning on querying database. I have a column with a JSON data type like this

[
  {
    "name": "Noctis Lucis Caellum",
    "unixTime": "1530810000"
  },
  {
    "name": "Stella Nox Fleuret",
    "unixTime": "1530811111"
  },
  {
    "name": "Ignis Scientia",
    "unixTime": "1530812222"
  },
]

[
  {
    "name": "Noctis Lucis Caellum",
    "unixTime": "1530810000"
  },
  {
    "name": "Stella Nox Fleuret",
    "unixTime": "1530811111"
  },
  {
    "name": "Ignis Scientia",
    "unixTime": "1530812222"
  },
]

I want to take one object with the oldest unixTime, then replace it with a new object, like:

{
  "name": "Gladiolus Amicitia",
  "unixTime": "1530813333"
}

{
  "name": "Gladiolus Amicitia",
  "unixTime": "1530813333"
}

So the final result is something like:

[
  {
    "name": "Gladiolus Amicitia",
    "unixTime": "1530813333"
  },
  {
    "name": "Stella Nox Fleuret",
    "unixTime": "1530811111"
  },
  {
    "name": "Ignis Scientia",
    "unixTime": "1530812222"
  },
]

[
  {
    "name": "Gladiolus Amicitia",
    "unixTime": "1530813333"
  },
  {
    "name": "Stella Nox Fleuret",
    "unixTime": "1530811111"
  },
  {
    "name": "Ignis Scientia",
    "unixTime": "1530812222"
  },
]

Can this be done using a query? Or is there best practices to do this (like using PHP approach)? Sorry for my bad english, and.. thank you for reading

6 Replies

ErickO•8mo ago

it's an array? or are you just showing the different json columns that there are it is possible but perhaps not the best idea, really depends on what you're doing here. 1- do you need this to be a JSON column? 2- is it an actual JSON column and not a TEXT column that you insert json-like strings into? 3- do you have a lot of data in there? last question is because you can just query for the whole thing and do it in PHP but if you have a lot of data returned then it might be a bad idea to do it locally and instead should be done in the database, the code for this to be done in MySQL is kinda lengthy tho so if you can and prefer to do it in php that's fine

Asheavenist•8mo ago

Yes.. And one cell will have max 5 object inside it. Later, object that have an oldest unixTime will change with the new one.

Asheavenist•8mo ago

1- Honestly, I don't know if I need it or not. Because I also have other options in designing this table:

Asheavenist•8mo ago

Based on the options above. I choose Table_Opt_1. But still, I'm confuse. Is my choice right..? 2- I don't know if it is actual JSON column or not. What I know is the data type that currently use on that column is JSON, not TEXT. 3- I'll set it max 5 object that will be dynamically change/patch/override because of user state. And each object only has about 5 keys. Any advice, sir?

ErickO•8mo ago

personally, if I can avoid saving actual JSON to the database I 100% avoid it, it's just much easier to save it as columns and do whatever you need to that way in table opt 3 all you'd have to do is select the columns of whatever ID you want and grab the MIN(unix_time) out of those that's all there is to it

Asheavenist•8mo ago

Aaah.. I see I'll try to use opt 3..Thank you for your advice

Gaming

Programming

Query on JSON data type. MySQL.