Question 161372 in Mysql Question posted in
The official MySQL documentation can be found here.

Can WHERE be used on JSON in MySQL?

I am trying to apply the where clause on json data instead of table and column. I have tried lots of way but didn’t get the result as I want.

Below is a query:

select 
CustomerName as 'CustomerName',
TRUNCATE(JSON_VALUE(BillData, '$[0].due'), 3) as 'Balance'
from 
(
select
CONCAT(cd.FirstName, ' ',cd.LastName) as 'CustomerName',
sb.billData as 'BillData'
from test.customerData cd
inner join test.customerbills sb on cd.customerCode = sb.customerCode
) as tb

BillData is a json.BillData contains data like below:

[{
"isPastDue":true,
"isPrinted":true,
"dueDate":"2023-03-28"
},
{
"isPastDue":false,
"isPrinted":true,
"dueDate":"2023-04-22"
},
{
"isPastDue":false,
"isPrinted":false,
"dueDate":"2023-05-06"
}]

Now, I want to get the dueDate of that node which isPastDue and isPrinted flag is false.

I have spend lots of time for get the proper data but didn’t found it. I found the solution like below but it’s not working.

(
SELECT JSON_EXTRACT(BillData, '$.dueDate') AS item
WHERE JSON_EXTRACT(BillData, '$.isPrinted') = 'false'
and JSON_EXTRACT(BillData, '$.isPastDue') = 'false'
) as 't1'

Next when I run the query then it’s showing null in the result. How can I achieve the result as per expectation?

Thank you in advance.

Tags:

2

Answers


  1. 0

    It looks like "BillData" is an array of objects, while you are trying to access a single object.
    Please try this approach:

    (
SELECT JSON_EXTRACT(BillData, '$[*].dueDate') AS item
WHERE JSON_EXTRACT(BillData, '$[*].isPrinted') = 'false'
and JSON_EXTRACT(BillData, '$[*].isPastDue') = 'false'
) as 't1'

    If this is not working, you have to go through the indexes of the array manually.

    Login or Signup to reply.
  2. 0

    I want to get the dueDate of that node which isPastDue and isPrinted
    flag is false

    You can use json_table to convert your array of objects into table as follows :

    SELECT *
FROM customerData cd
CROSS JOIN JSON_TABLE(
                       cd.billData,
                      '$[*]' 
                       COLUMNS (
                       isPastDue tinyint PATH '$.isPastDue',
                       isPrinted tinyint PATH '$.isPrinted',
                       dueDate DATE PATH '$.dueDate'
                      ) ) j

    So your query can be something like this :

    SELECT CONCAT(cd.FirstName, ' ',cd.LastName) as 'CustomerName', j.*
FROM customerData cd
inner join customerbills sb on cd.customerCode = sb.customerCode
CROSS JOIN JSON_TABLE(
                       cd.billData,
                      '$[*]' 
                       COLUMNS (
                       isPastDue tinyint PATH '$.isPastDue',
                       isPrinted tinyint PATH '$.isPrinted',
                       dueDate DATE PATH '$.dueDate'
                      ) ) j
where isPastDue = 0 and isPrinted = 0

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search