json_contains within an array returns empty set

Question

I have a json array like

{  
   "response":[  
      {  
         "Rate Lock":"Yes",
         "Loan Amount":"1M - 2M",
         "Credit Score":"800",
         "Pre-Approved":"Yes",
         "Mortgage Type":"15 ARM",
         "Property Type":"Commercial",
         "Forecasted Close Date":"2018-07-12"
      }
   ]
}

in a column called 'custom_response'

select * from custom_deals_response where json_contains(`custom_response`,'"2018-07-12"')

returns and empty set. What am I doing wrong?

Maxim Kasyanov · Accepted Answer · 2018-07-14 08:25:07Z

1

JSON_CONTAINS has 3 parameters(targer column, candidate, path(optional)). In you data structure your targer search column location inside response objects. You have to set 3 params to specific path where mySql have to search. Documentation reference

JSON_CONTAINS(target, candidate[, path])

 select * from custom_deals_response 
    where json_contains(`custom_response`,'{"Forecasted Close Date":"2018-07-12"}','$.response');

edited Jul 14, 2018 at 8:25

answered Jul 13, 2018 at 19:38

Maxim Kasyanov

1,0987 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

zx485 Over a year ago

It would be nice if you'd surround your code by some explanation.

Maxim Kasyanov Over a year ago

@zx485 Thank for comment. I've added description to answer.

Collectives™ on Stack Overflow

json_contains within an array returns empty set

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related