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

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?

CodePudding user response：

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.

CodePudding user response：

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