0

I'm trying to figure out how to make the following query using the documents in Mysql 8:

Given the following JSON documents:

{
    "caseOpener": "john doe"
    "allStatus": [
        {
            "date": "2020-10-18",
            "label": "IN_PROGRESS"
        },
        {
            "date": "2022-01-02",
            "label": "VALIDATION"
        }
    ]
},

{
    "caseOpener": "marrie doe"
    "allStatus": [
        {
            "date": "2020-10-18",
            "label": "IN_PROGRESS"
        }
        ,
        {
            "date": "2021-01-09",
            "label": "VALIDATION"
        },

        {
            "date": "2022-01-02",
            "label": "CLOSED"
        }
    ]
}

I have to get all the records based on the label of the status and the date of this given status. For example, I need to get all the records that reached the status 'VALIDATION' after 2022-01-01, It could be something like this:

SELECT * from table WHERE
JSON_SEARCH(doc, 'one', 'VALIDATION', NULL, '$.allStatus[*].label') IS NOT NULL
AND doc->>$.allStatus[*].date > 2022-01-01 // didnt find how to do it properly

But of course it is wrong because:

  1. I didnt find how to compare dates that are in the array inside the json (I tried using the JSON_SEARCH, but it didnt find some way to apply the rule of 'greater than' using the JSON_SEARCH, just the exactly match)
  2. The search will not compare the values of the same 'line' of the array. In this case, it will return both records instead of just the first, because the second have one Status with label "VALIDATION" and one date that is > than 2022-01-01, but they are not in the same 'line' of the array.

Thank you for the help and happy new year :)

Update:

I have been able to do this query using JSON_TABLE, however it seems to add too much complexity to the query. Is there any other way to do it?

SELECT * FROM table WHERE 
  (SELECT count(*) FROM JSON_TABLE(doc, '$.allStatus[*]' COLUMNS 
    (label VARCHAR(40)  PATH '$.label', date VARCHAR(100) PATH '$.date'))  
   status WHERE label = 'VALIDATION' and date> '2022-01-01') > 0
Jeff Mira
  • 15
  • 4
  • `JSON_TABLE` is the right way to go. Or don't store data as JSON. – Salman A Jan 18 '22 at 12:25
  • Thank for the reply @SalmanA, unfortunatelly I'm migrating an application from Couchbase to Mysql, so I have to use the JSON format. I'll use the JSON_TABLE then. – Jeff Mira Jan 19 '22 at 10:56
  • If this is a typical example of what you would do with the data then it would be best to store the data as tables. See https://stackoverflow.com/questions/15367696/storing-json-in-database-vs-having-a-new-column-for-each-key – Salman A Jan 19 '22 at 11:05

0 Answers0