0

I have a mysql database with table orders, where all installments are kept in one column called installmentData

example record looks like this:

[
    {
        "amount": "79.00",
        "paid_at": "2019-07-24",
        "payment_date": "2019-07-27"
    },
    {
        "amount": "80.00",
        "paid_at": false,
        "payment_date": "2019-08-27"
    },
    {
        "amount": "80.00",
        "paid_at": false,
        "payment_date": "2019-09-27"
    }
]

What I would like to get is the first record of payment_date from json where paid_at is false

I tried something like this

SELECT 
    JSON_EXTRACT(installmentData, '$[*].paid_at') as paidArray,
    JSON_EXTRACT(installmentData, '$[*].payment_date') as dateArray
FROM 
    orders 
WHERE 
    JSON_CONTAINS(JSON_EXTRACT(installmentData, '$[*].paid_at'), 'false') IS TRUE 
AND 
    resignation IS FALSE 
ORDER BY `orders`.`status` DESC

this query return this

["2019-07-24", false, false]    ["2019-07-27", "2019-08-27, 2019-09-27]

and I want to only return

[false] ["2019-08-27"]

Is it possible to get that specific value in the example above?

Salim Djerbouh
  • 10,719
  • 6
  • 29
  • 61
Piczak
  • 11
  • 2
  • Are you using MySQL 8.0? You would have to do it with `JSON_TABLE()`, but that function is not implemented in past versions of MySQL before 8.0. – Bill Karwin Sep 02 '19 at 16:38
  • 1
    I posted an answer for a similar case here: https://stackoverflow.com/q/55809822/20860 – Bill Karwin Sep 02 '19 at 16:40
  • Thank you for suggestion, I'm using mysql 5.7 so i'll have to upgrade mysql and test it. – Piczak Sep 02 '19 at 17:15
  • 1
    It would be better if you could store data in normal columns, which would be far more efficient, more compact, and easier to write queries for. If you're using JSON search functions in your `WHERE` clause, that's an indication that you've designed your database wrong. – Bill Karwin Sep 02 '19 at 18:24
  • this is just for migrations to update order statuses – Piczak Sep 02 '19 at 20:55

0 Answers0