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?

score 1 · Answer 1 · answered Dec 23 '22 at 13:56

1

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 '22 at 13:56

Elie Asmar

2,995
4
17
30

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. – Vadim Brook Dec 23 '22 at 14:02
2

SELECT JSON_UNQUOTE(JSON_ARRAYAGG(jobs)) FROM ( Select * FROM tbl_Jobs ... the query in the answer) t – Elie Asmar Dec 23 '22 at 14:09
I don't actually get results with SELECT * FROM table WHERE JSON_EXTRACT(jobs, "$[*].target") = JSON_EXTRACT(jobs, "$[*].done") – Vadim Brook Dec 23 '22 at 14:18

Bill Karwin · Accepted Answer · 2022-12-23T15:52:00.100

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 '22 at 15:52

answered Dec 23 '22 at 15:33

Bill Karwin

538,548
86
673
828

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. – Vadim Brook Dec 23 '22 at 15:38
1

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(). – Bill Karwin Dec 23 '22 at 15:41

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

2 Answers2