0

I have some nested json data that i get using the json_object() function. Now I need to to check whether a certain value exists for a particular key which I am using as follows in my query:-

select json_object(..) from tblxyz 
  where json_contains(columnname, '{"MyName" : "FirstName LastName"}')

The problem with this approach is that I require the entire string "FirstName LastName" to find this row in the actual result. Now I need to perform a wild card search. That is:-

select json_object(..) from tblxyz 
  where json_contains(columnname, '{"MyName" : "FirstName"}')

should also return this entry to me in the final result. Can someone please suggest any workaround?

1

Sorry, but you are really expecting too much from JSON in a database.

Any JSON "fields" that need to be searched/sorted/joined/etc in a relational database manner should be pulled out (or at least copied out) into their own column(s).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.