Requirement
we would like to bring support for contains operator in cps-path.
...
CPS-1272
Issues & decisions
# | Json Data | | output |
|---|
| 1 | Below is the sample data , Here are ways to use contains keyword : | Expand |
|---|
{
"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"
}
]
} |
|
|---|
] }}{ "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')
|
Banks"
],
"pub_year":1994
} |
|
Pullman"
],
"pub_year":1995
} |
|
Native Query for contains keyword
...
# | Query | Output |
|---|
| 1 | cpsdb=# SELECT * FROM FRAGMENT WHERE anchor_id = 4 and attributes->>'lang' like '%en%'; |
| Expand |
|---|
{
"lang": "en",
"price": 699,
"title": "The Golden Compass",
"authors": [
"Philip Pullman"
],
"pub_year":1995
}
{
"lang": "english",
"price": 895,
"title": "Feersum Endjinn",
"authors": [
"Iain M. Banks"
],
"pub_year":1994
} |
|
|---|
| 2 | cpsdb=# SELECT * FROM FRAGMENT WHERE anchor_id = 4 and attributes->>'lang' ilike '%En%'; |
| Expand |
|---|
{
"lang": "en",
"price": 699,
"title": "The Golden Compass",
"authors": [
"Philip Pullman"
],
"pub_year":1995
}
{
"lang": "English",
"price": 895,
"title": "Feersum Endjinn",
"authors": [
"Iain M. Banks"
],
"pub_year":1994
} |
|
|---|
| 3 | cpsdb=# SELECT * FROM FRAGMENT WHERE anchor_id = 4 and attributes->>'lang' like 'en'; |
| Expand |
|---|
{
"lang": "en",
"price": 699,
"title": "The Golden Compass",
"authors": [
"Philip Pullman"
],
"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 to LIKE, except that it interprets the pattern using the SQL standard's definition of a regular expression
...
# | Query | Output |
|---|
| 1 | cpsdb=# SELECT * FROM FRAGMENT WHERE anchor_id = 3 and attributes->>'pub_year'similar to '%(94|95)%'; |
| Expand |
|---|
{
"lang": "en",
"price": 699,
"title": "The Golden Compass",
"authors": [
"Philip Pullman"
],
"pub_year":1995
}
{
"lang": "english",
"price": 895,
"title": "Feersum Endjinn",
"authors": [
"Iain M. 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
...
