How to get a specific object in an JSON array in MySQL?

Question

I have a JSON column "jobs" that looks like this:

[
    {
      "id": "1",
      "done": "100",
      "target": "100",
      "startDate": "123123132",
      "lastAction": "123123132",
      "status": "0"
    },
    {
      "id": "2",
      "done": "10",
      "target": "20",
      "startDate": "2312321",
      "lastAction": "2312321",
      "status": "1"
    }
]

I want to filter the array by object key values. For example: To find all items that have target > done, status != 0 and lastAction is yesterday to get response like this:

[
    {
      "id": "1",
      "done": "19",
      "target": "100",
      "startDate": "123123132",
      "lastAction": "123123132",
      "status": "0"
    }
]

I know I can extract the data to a JSON_TABLE() to do the filtering but I don't get the original object back(unless I recreate it back) and the solution is not dynamic.

Can this kind of array filtering can really be done in MySQL?

Elie Asmar · Accepted Answer · 2022-12-23 13:56:24Z

2

Yes it is possible to do it using the JSON_EXTRACT and JSON_SEARCH functions.

Let's say your table is named tbl_Jobs and the jobs column is of type JSON.

SELECT * FROM tbl_Jobs
WHERE JSON_EXTRACT(jobs, "$[*].target") = JSON_EXTRACT(jobs, "$[*].done")
AND JSON_EXTRACT(jobs, "$[*].status") != 0
AND JSON_SEARCH(jobs, 'one', DATE_SUB(CURDATE(), INTERVAL 1 DAY), NULL, "$[*].lastAction") IS NOT NULL

answered Dec 23, 2022 at 13:56

Elie Asmar

3,2057 gold badges21 silver badges33 bronze badges

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

3 Comments

Vadim Brook Over a year ago

Thanks. But it doesn't solves it. I want the object that matches the conditions as the result, not the entire row that has the array in it.

Elie Asmar Over a year ago

SELECT JSON_UNQUOTE(JSON_ARRAYAGG(jobs)) FROM ( Select * FROM tbl_Jobs ... the query in the answer) t

Vadim Brook Over a year ago

I don't actually get results with SELECT * FROM table WHERE JSON_EXTRACT(jobs, "$[].target") = JSON_EXTRACT(jobs, "$[].done")

Bill Karwin · Accepted Answer · 2022-12-23 15:52:00Z

1

SELECT JSON_PRETTY(JSON_EXTRACT(jobs.jobs, CONCAT('$[', j.rownum-1, ']'))) AS object
FROM jobs
CROSS JOIN JSON_TABLE(
  jobs.jobs, '$[*]' COLUMNS(
    rownum for ordinality,
    done int path '$.done',
    target int path '$.target',
    status int path '$.status'
  )
) as j
WHERE j.target > j.done AND j.status != 0;

You also mentioned a condition on lastAction, but the example values you gave are not valid dates, so I'll leave that enhancement to you. The example above demonstrates the technique.

edited Dec 23, 2022 at 15:52

answered Dec 23, 2022 at 15:33

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

2 Comments

Vadim Brook Over a year ago

Thank you. I does work for filtering and reconstructing the object but not dynamic. This solution hard-codeds the keys, if another key is added(for example some extra job data) the query won't get it. The idea is to query the user table for his jobs, do a job and update the statuses while allowing extra data to be passed.

Bill Karwin Over a year ago

No, you don't need to do that. You only need to add the keys to JSON_TABLE() for fields you will have conditions on, plus the rownum for ordinality. If there are other keys, the JSON_EXTRACT() expression in the select-list will get the full object, including fields that you have not referenced in the JSON_TABLE().

Collectives™ on Stack Overflow

How to get a specific object in an JSON array in MySQL?

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related