15 replies

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

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

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

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

Query on JSON data type. MySQL.

Similar Threads

Query on JSON data type. MySQL.

Similar Threads

Similar Threads

Similar Threads