Native query for Contains Operator using Like Keyword :
#
Issue
Notes
Decisions
1
Which keyword to use ? Do we want case sensitivity or not? Do we follow the Xpath contains or do we become specific?
like %en%
ilike %En%
like 'en'
As per discussion , with Toine Siebelink , community call like keyword would has consistency which support case sensitive attribute values.
Need to discuss with stakeholders.
As per discussion in community call , decided to go with like keyword more consistent support case sensitivity.
#
Json Data
CPS-PATH Syntax
output
Output
1
Below
is
the
sample
data
, Here are ways to use contains keyword :
, Here are ways to use contains keyword :
Code Block
title
Json data
collapse
true
{
{
"test:bookstore":{
"bookstore-name": "Chapters",
"categories": [
{
"code": "01",
"name": "SciFi",
"books": [
{
"authors": [
"Iain M. Banks"
],
"lang": "english",
"price": "895",
"pub_year": "1994",
"title": "Feersum Endjinn"
}
]
},
{
"name": "kids",
"code": "02",
"books": [
{
"authors": [
"Philip Pullman"
],
"lang": "Science",
"price": "699",
"pub_year": "1995",
"title": "The Golden Compass"
}
]
}
]
}
}
<cps-path>
(
contains
'[@leafname,'
<string-value>
']'
)
Examples
//books[contains(@lang,'en')
//books[contains(@pub_year,'99')
{
Code Block
title
Json Response
collapse
true
{
"lang": "en",
"price": 895,
"title": "Feersum Endjinn",
"authors": [
"Iain M. Banks"
],
"pub_year":1994
}
{
"lang": "en",
"price": 699,
"title": "The Golden Compass",
"authors": [
"Philip Pullman"
],
"pub_year":1995
}
Native Query for contains keyword
...
_ : Matches any single character.
#
Query
Output
1
cpsdb=# SELECT * FROM FRAGMENT WHERE anchor_id = 4 and attributes->>'lang' like '%en%';
{
Code Block
title
Json Response
collapse
true
{
"lang":"en",
"price":699,
"title":"TheGoldenCompass",
"authors":[
"PhilipPullman"
],
"pub_year":1995
}
{
"lang":"english",
"price":895,
"title":"FeersumEndjinn",
"authors":[
"IainM.Banks"
],
"pub_year":1994
}
2
cpsdb=# SELECT * FROM FRAGMENT WHERE anchor_id = 4 and attributes->>'lang' ilike '%En%';
{
Code Block
title
Json Response
collapse
true
{
"lang":"en",
"price":699,
"title":"TheGoldenCompass",
"authors":[
"PhilipPullman"
],
"pub_year":1995
}
{
"lang":"English",
"price":895,
"title":"FeersumEndjinn",
"authors":[
"IainM.Banks"
],
"pub_year":1994
}
3
cpsdb=# SELECT * FROM FRAGMENT WHERE anchor_id = 4 and attributes->>'lang' like 'en';
Code Block
title
Json Response
collapse
true
{
"lang":"en",
"price":699,
"title":"TheGoldenCompass",
"authors":[
"PhilipPullman"
],
"pub_year":1995
}
2.Using SIMILAR TO Regular Expression Keyword :
The only difference between between like and similar to is to pattern matches the given string. It is similar toLIKE, except that it interprets the pattern using the SQL standard's definition of a regular expression
...
|denotes alternation (either of two alternatives).
*denotes repetition of the previous item zero or more times.
+denotes repetition of the previous item one or more times.
?denotes repetition of the previous item zero or one time.
{m}denotes repetition of the previous item exactlymtimes.
{m,}denotes repetition of the previous itemmor more times.
{m,n}denotes repetition of the previous item at leastmand not more thanntimes.
Parentheses()can be used to group items into a single logical item.
A bracket expression[...]specifies a character class, just as in POSIX regular expressions.
#
Query
Output
1
cpsdb=# SELECT * FROM FRAGMENT WHERE anchor_id = 3 and attributes->>'pub_year'similar to '%(94|95)%';
Code Block
title
Json Response
collapse
true
{
"lang":"en",
"price":699,
"title":"TheGoldenCompass",
"authors":[
"PhilipPullman"
],
"pub_year":1995
}
{
"lang":"english",
"price":895,
"title":"FeersumEndjinn",
"authors":[
"IainM.Banks"
],
"pub_year":1994
}
Performance wise : As we are not making much changes for query , the performance is similar to existing query will not effect much
Implementation of Contains Operator
1.Update antlr parser to recognize this pattern 2.Implement required (native) query 3.Add Integration tests for a.filter on string leaf-value b.filter on Integer leaf-value 4.Update documentation 5.demo to team
Limitations
1. contains condition is case sensitive. 2. Only leaves can be used, leaf-list are not supported. 3. Only string and integer values are supported, boolean and float values are not supported. 4. When empty value is passed with contains it returns all the nodes that has given leaf element.