Asheavenist Posts - Answer Overflow

Asheavenist

•Created by Asheavenist on 10/28/2023 in #back-end

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

16 replies

KPCKevin Powell - Community

•Created by Asheavenist on 7/12/2023 in #back-end

Querying

5 replies

Gaming

Programming