2

I have problem in mysql json that I want to compare one item of object in json array. example: in my table i have column aJson that is array :

[{"Name":"Mohsen","Code":3},{"Name":"Ali","Code":5},{"Name":"Reza","Code":2}]

I want to find the records where Code is greater than 3

output:

[{"Name":"Mohsen","Code":3},{"Name":"Ali","Code":5},{"Name":"Reza","Code":8}]

same for equal,less,like ...

Charlie
  • 22,886
  • 11
  • 59
  • 90
Mohsen
  • 19
  • 4

2 Answers2

2

I think it can be solved using JSON_EXTRACT which is available in MySQL version >= 5.7

mysql> SELECT mycolumn
     > FROM mytable
     > WHERE mycolumn->"$.Code" > 3

-> is an alias for JSON_EXTRACT

Ref:

deerawan
  • 8,002
  • 5
  • 42
  • 51
-1

thanks it works as this ...

> mysql> SELECT mycolumn
>      > FROM mytable
>      > WHERE mycolumn->"$[*].Code" > 3

but what about this sample 

[
  {
    "Name": "Mohsen",
    "Code": 10,
    "Arrays": [
      {
        "Name": "Mohsen",
        "Code": 6
      },
      {
        "Name": "Ali",
        "Code": 7
      },
      {
        "Name": "Reza",
        "Code": 8
      }
    ]
  },
  {
    "Name": "Ali",
    "Code": 11,
    "Arrays": [
      {
        "Name": "Mohsen",
        "Code": 6
      },
      {
        "Name": "Ali",
        "Code": 7
      },
      {
        "Name": "Reza",
        "Code": 8
      }
    ]
  },
  {
    "Name": "Reza",
    "Code": 12,
    "Arrays": [
      {
        "Name": "Mohsen",
        "Code": 6
      },
      {
        "Name": "Ali",
        "Code": 7
      },
      {
        "Name": "Reza",
        "Code": 8
      }
    ]
  }
]

find records that Arrays.Code > 3

the follow query did not work ...

SELECT * from table WHERE aJson->"$[*].Arrays[*].Code" > 7

return all records

Mohsen
  • 19
  • 4
  • Don't use the *answer* section for asking *questions*. Just upvote and/or accept an answer here and then ask a new question, referencing this thread. – Strawberry Feb 19 '20 at 07:25