Problem with comparing strings

query
n1ql

#1

Hi,
SELECT * FROM content r WHERE REGEX_LIKE(meta().id , “user:+.*.sessions”) and any i in object_pairs®
satisfies i.val.id=1658 end;

above query giving proper result.

but
SELECT * FROM content r WHERE REGEX_LIKE(meta().id , “user:+.*.sessions”) and any i in object_pairs®
satisfies i.val.host=“abc.def.in” end;

this is not working properly due to dots in “abc.def.in”.
please tell how to overcome it.


#2

I don’t think there is any issue.

The following is works.

insert into default values ("user:f01.sessions",{ "1hge5ci.f5e4.5d2mc58jn.8de3aa74": { "_id": "1hge5ci.f5e4.5d2mc58jn.8de3aa74", "cr": 1512998788, "host": "df", "id": null, "ip": "10.150.244.55", "la": 1512998788 }, "1hge5ci.f734.5d2mc7xoc.8f44e929": { "_id": "1hge5ci.f734.5d2mc7xoc.8f44e929", "cr": 1512998970, "host": "sd", "id": null, "ip": "10.150.244.55", "la": 1512998970 }, "_t": "user", "_ts": 1512043010 } );

SELECT * 
FROM default d 
WHERE ANY v IN OBJECT_VALUES(d) SATISFIES v.ip = "10.150.244.55" END;

#3

But in my case it providing random data while searching with other data it is working fine


#4

The following is working fine.

insert into default values ("user:f01.sessions",{ "1hge5ci.f5e4.5d2mc58jn.8de3aa74": { "_id": "1hge5ci.f5e4.5d2mc58jn.8de3aa74", "cr": 1512998788, "host": "df", "id": null, "ip": "10.150.244.55", "la": 1512998788 }, "1hge5ci.f734.5d2mc7xoc.8f44e929": { "_id": "1hge5ci.f734.5d2mc7xoc.8f44e929", "cr": 1512998970, "host": "sd", "id": null, "ip": "10.150.244.55", "la": 1512998970 }, "_t": "user", "_ts": 1512043010 } );
SELECT *
FROM default d
WHERE REGEX_LIKE(meta(d).id , "user:+.*.sessions") AND ANY v IN OBJECT_VALUES(d) SATISFIES v.ip = "10.150.244.55" END;

Provide the document key, document, query and expected output.


#5

I can’t provide you doc. but i can explain you. doc is saved in meta().id = user:n.sessions where n(user id) is dynamic. so i use regex_like to get the doc it is providing all docs but when i am searching doc with specific host it is producing random result. while when I search for any other field than host and ip it is producing excat result.


#6

What is CB version you are using. You can try latest version. What is index.
Post the EXPLAIN plan.
Sample document and query provided works.
You can also use META(d).id LIKE “user:%.sessions”
If you still have issue provide the sample document with by removing fields (only keep ip(fake it), document key).


#7

CB version: 5.0.1,
it consist a bucket content and type ‘user’ where session data saved with meta().id = user:uid.sessions and user main data saved with meta().id = user.uid .

When i apply search on meta().id using query
SELECT * FROM content as t WHERE REGEX_LIKE(meta().id , “user:+.*.sessions”)
OR
SELECT * FROM content as t WHERE meta().id LIKE “user:+.%.sessions”

both giving result somthing like this
[{
“content”: {
“1hge5ci.f5e4.5d2mc58jn.8de3aa74”: {
“_id”: “1hge5ci.f5e4.5d2mc58jn.8de3aa74”,
“cr”: 1512998788,
“host”: “df.ff.in”,
“id”: null,
“ip”: “12.151.124.55”,
“la”: 1512998788
},
“1hge5ci.f734.5d2mc7xoc.8f44e929”: {
“_id”: “1hge5ci.f734.5d2mc7xoc.8f44e929”,
“cr”: 1512998970,
“host”: “sd.ccc.in”,
“id”: null,
“ip”: “12.151.124.55”,
“la”: 1512998970
},
“_t”: “user”,
“_ts”: 1512043010
}
}, {
“content”: {
“2xrtpb.812d.5b2a2dm4w.9731565b”: {
“_id”: “2xrtpb.812d.5b2a2dm4w.9731565b”,
“cr”: 1500372687,
“host”: “12.151.124.55:11111”,
“id”: “1”,
“ip”: “124.10.0.1”,
“la”: 1500372687
},
“2xrvow.d9aa.5dbphuaz4.7957cc8f”: {
“_id”: “2xrvow.d9aa.5dbphuaz4.7957cc8f”,
“cr”: 1514976997,
“host”: “edfdf.kk.in”,
“id”: 1,
“ip”: “10.154.242.108”,
“la”: 1514976997
},
“2xrvow.ff2d.5dbor8wrw.8d17b788”: {
“_id”: “2xrvow.ff2d.5dbor8wrw.8d17b788”,
“cr”: 1514972545,
“host”: “dsdfs.ll.in”,
“id”: 1,
“ip”: “10.177.888.108”,
“la”: 1514972545
},
“5kk728.938a.5dke6b7hh.8d972e0e”: {
“_id”: “5kk728.938a.5dke6b7hh.8d972e0e”,
“cr”: 1516867653,
“host”: “deff.sdfds.com”,
“id”: 1,
“ip”: “223.165.28.230”,
“la”: 1516867653
},
“5kk728.fc11.5c36n33ho.10c0c220”: {
“_id”: “5kk728.fc11.5c36n33ho.10c0c220”,
“cr”: 1509342992,
“host”: “def.kk.in”,
“id”: “1”,
“ip”: “23.151.888.999”,
“la”: 1509342992
},
“_t”: “user”,
“_ts”: 1501261627,
“api-YLZ0NMbQaAZ0NMbQaAd-Nkb-_E40aAZQIJbKwkYCm”: {
“_id”: “api-YLZ0NMbQaAZ0NMbQaAd-Nkb-_E40aAZQIJbKwkYCm”,
“cr”: 1511798364,
“host”: “abc.sdc.com”,
“id”: 1,
“ip”: “23.151.888.999”,
“la”: 1511798364
},
“group”: “editor”
}
}]

now when i am trying to get data for given host or ip using below query it is producing random result. These quries are working fine only when i search for id

SELECT * FROM content r WHERE META®.id LIKE “user:%.sessions” and any v in OBJECT_PAIRS®
satisfies v.val.ip=“10.150.244.27” end;
OR
SELECT * FROM content r WHERE META®.id LIKE “user:%.sessions” and any v in OBJECT_VALUES®
satisfies v.ip=“10.150.244.27” end;

OR

SELECT * FROM content r WHERE REGEX_LIKE(meta().id , “user:+..sessions") and any v in OBJECT_PAIRS®
satisfies v.val.ip=“10.150.244.27” end;
OR
SELECT * FROM content r WHERE REGEX_LIKE(meta().id , "user:+.
.sessions”) and any v in OBJECT_VALUES®
satisfies v.ip=“10.150.244.27” end;


#8

i noticed one more thing it returning those content object whoose any object consist host matching to given host. it is working fine in case of id because id is unique and hence it return only content object with given id.


#9

what do you mean by random results. Please post be expected output and actual output.
Do you mean it gives whole document ? Are you expecting only matched sub-object of the host?
You are projecting whole document and predicate says when any host matched project it.

In that case try this.

SELECT pv.val 
FROM content r 
UNNEST OBJECT_PAIRS(r) AS pv
WHERE META(r).id LIKE "user:%.sessions" AND  pv.val.ip="10.150.244.27";

#10

Hey thanks for getting all my points… I got my expected result .
Thanks