mysql array of objects compare items

Question

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 ...

deerawan · Accepted Answer · 2020-02-19 06:34:32Z

3

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:

https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html#operator_json-column-path

answered Feb 19, 2020 at 6:34

deerawan

8,5035 gold badges46 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mohsen · Accepted Answer · 2020-02-19 06:57:07Z

-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

answered Feb 19, 2020 at 6:57

Mohsen

194 bronze badges

1 Comment

Strawberry Over a year ago

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.

Collectives™ on Stack Overflow

mysql array of objects compare items

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related