sql query with multiple partial match condition

Question

i have a table column looks like below. what is the sql query statement i can use to have multiple partial match conditions?

search by ID or Name

if search abc then list the row A1 , row A2

if search test then list the row A1 , row A2, row 3

if search ghj then list the row A2

i was trying this but nothing return:

 SELECT * FROM table where colB LIKE '"ID":"%abc%"'

table column

updating data in text

{"ItemId":"123","IDs":[{"ID":"abc","CodingSystem":"cs1"}],"Name":"test itemgh"}

{"ItemId":"123","IDs":[{"ID":"ghj","CodingSystem":"cs1"}],"Name":"test abc"}

{"ItemId":"123","IDs":[{"ID":"defg","CodingSystem":"cs1"}],"Name":"test 111"}

Please tag with your database (e.g. MySQL, SQL Server, Oracle). You will probably have to use JSON functions to handle your requirement. — Tim Biegeleisen, Oct 16 '19 at 08:51
Postgres or Microsoft SQL Server? The syntax for JSON functions will be very different — a_horse_with_no_name, Oct 17 '19 at 05:38

sanastasiadis · Answer 1 · 2019-10-18T11:20:24.657

JSON parsing

Oracle

Looked into the JSON parsing capabilities of Oracle and I managed to make running a query like this:

select * from table t where json_exists(t.colB, '$.IDs[?(@.ID=="abc")]') or json_exists(t.colB, '$.IDs?(@.name=="abc"')

And inside the same JSON query expression:

select * from table t where json_exists(t.colB, '$.IDs[?(@.ID=="abc" || @.name=="abc")]')

The call of function json_exists() is the key to this. The first parameter can be a VARCHAR2, and I also tried with a BLOB containing text, and it works. The second parameter is the path to your json object attribute that needs to be tested, with the condition.

I wrote two ORed conditions for the ID and for the Name, but maybe there is a better JSON query expression you can use to include them both.

More information about json_exists() function here.

Postgres

There is a JSON datatype in Postgres that supports parsing in queries. So, if your colB column is declared as JSON you can do something like this:

select * from table where colB->>'Name' LIKE '%abc%';

And in order to have available the array elements of the IDs array, you should use the function json_array_elements().

select * from table, json_array_elements(colB->'IDs') e where colB->>'Name' LIKE '%abc%' or e->>'ID' = 'abc';

Check an example I created for you here.

Here is an online tool for online testing your JSON queries.

Check also this question in SO.

MSSQL Server 2017

I made a couple of tests also with MS SQL Server, and I managed to create an example searching for partial matching in the name field.

select * from table where JSON_VALUE(colB,'$.Name') LIKE '%abc%';

And finally I arrived to a working query that does partial match to the Name field and full match to the ID field like this:

select * from table t
    CROSS APPLY OPENJSON(colB, '$.IDs') WITH (
        ID VARCHAR(10),
        CodingSystem VARCHAR(10)
    ) e
where JSON_VALUE(t.colB,'$.Name') LIKE '%abc%'
or e.ID = 'abc';

The problem is that we need to open the IDs array, and make something like a table from it, that can be queried also by accessing its columns.

The example I created is here.

LIKE text query

Your tries are good but you misplace the % symbols. They have to be first and last in your given string:

If you want the ID to be the given value:

SELECT * FROM table where colB LIKE '%"ID":"abc"%'

If the given value can be anywhere, then don't put the "ID" part:

SELECT * FROM table where colB LIKE '%abc%'

If the given value can be only on the ID or Name field then:

SELECT * FROM table where colB LIKE '%"ID":"abc"%' OR colB LIKE '%"Name":"abc"%'

And because you are giving hard-coded identifiers of fields (eg ID and Name) that can be in variable case:

SELECT * FROM table where lower(colB) LIKE '%"id":"abc"%' OR lower(colB) LIKE '%"name":"abc"%'

Assuming that the number of spaces do not vary between the : character and the value or the name of the properties.

For partial matching you can use more % in between like '%"name":"%abc%"%':

SELECT * FROM table where lower(colB) LIKE '%"id":"abc"%' OR lower(colB) LIKE '%"name":"%abc%"%'

Regular Expressions

A different option would be to test with regular expressions. Consider checking this: Oracle extract json fields using regular expression with oracle regexp_substr

. your first solution return no record. second solution isn't the one i wanted as i want to search at the ID or Name only — user2201789, Oct 16 '19 at 10:08
@user12158726 Is it possible to copy and paste the example data in the question? Currently there is an image with the test data. — sanastasiadis, Oct 16 '19 at 10:26
@user12158726: you should treat that string as a proper JSON value and use the JSON functions your database supports. Using `LIKE` for this, isn't a good idea — a_horse_with_no_name, Oct 16 '19 at 10:33
SELECT * FROM table where colB LIKE '%"ID":"abc"%' this one does not work. no record return. i guess i have to learn about jsonvalue statement — user2201789, Oct 18 '19 at 02:51
@user12158726 Check the latest update of my answer. I updated with Postgres information. Check the example I created for you also. — sanastasiadis, Oct 18 '19 at 09:44
@user12158726: can you please finally tell us which DBMS product are you really using? — a_horse_with_no_name, Oct 18 '19 at 10:01
@user12158726 I updated my answer with updated info about MSSQL Server. — sanastasiadis, Oct 18 '19 at 11:21
i have tried For partial matching , if actual result, name is not match but id is match ,then the query give me 0 record which is not right. — user2201789, Oct 21 '19 at 01:32
@user12158726 The idea is the same. If you want partial matching for ID also, then you put `LIKE` operator and you can use `%abc%` with wildcards. — sanastasiadis, Oct 21 '19 at 09:22
i guess you guys misunderstand the requirement. i know LIKE operator, just when it needs to have 2 combination of fields condition, it does not works. — user2201789, Oct 21 '19 at 09:57
@user12158726 Then it sounds like you have to change the operator 'OR' with an 'AND'. — sanastasiadis, Oct 21 '19 at 10:04