Query for nested json

SQL++
,
1

Hello, I am new in couchbase and trying to write a query for nested JSON

SELECT  * FROM `content`
  WHERE REGEX_LIKE(meta().id , "user:+.*.sessions")
  LIMIT 2

above query returns

[
  {
    "content": {
      "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
    }
  },
  {
    "content": {
      "2xrtpb.812d.5b2a2dm4w.9731565b": {
        "_id": "2xrtpb.812d.5b2a2dm4w.9731565b",
        "cr": 1500372687,
        "host": "sd",
        "id": "1",
        "ip": "127.0.0.1",
        "la": 1500372687
      },
      "2xrvow.d9aa.5dbphuaz4.7957cc8f": {
        "_id": "2xrvow.d9aa.5dbphuaz4.7957cc8f",
        "cr": 1514976997,
        "host": "edfdf",
        "id": 1,
        "ip": "10.150.243.108",
        "la": 1514976997
      },
      "2xrvow.ff2d.5dbor8wrw.8d17b788": {
        "_id": "2xrvow.ff2d.5dbor8wrw.8d17b788",
        "cr": 1514972545,
        "host": "dsdfs",
        "id": 1,
        "ip": "10.150.243.108",
        "la": 1514972545
      },
      "5kk728.938a.5dke6b7hh.8d972e0e": {
        "_id": "5kk728.938a.5dke6b7hh.8d972e0e",
        "cr": 1516867653,
        "host": "deff",
        "id": 1,
        "ip": "223.165.28.230",
        "la": 1516867653
      },
      "5kk728.fc11.5c36n33ho.10c0c220": {
        "_id": "5kk728.fc11.5c36n33ho.10c0c220",
        "cr": 1509342992,
        "host": "def",
        "id": "1",
        "ip": "10.150.176.212",
        "la": 1509342992
      },
      "_t": "user",
      "_ts": 1501261627,
      "api-YLZ0NMbQaAZ0NMbQaAd-Nkb-_E40aAZQIJbKwkYCm": {
        "_id": "api-YLZ0NMbQaAZ0NMbQaAd-Nkb-_E40aAZQIJbKwkYCm",
        "cr": 1511798364,
        "host": "abc",
        "id": 1,
        "ip": "10.150.180.107",
        "la": 1511798364
      },
      "group": "editor"
    }
  }
]

now i want object with given host so i tried this

SELECT  * FROM `content`  as r
  WHERE REGEX_LIKE(meta().id , "user:+.*.sessions")
  AND "df" IN r[*].host

AND

SELECT * FROM `content`  as r
  WHERE REGEX_LIKE(meta().id , "user:+.*.sessions")
  AND any h in r[*] satisfies h.host='df' end;

both giving

{
  "results": []
}

“1hge5ci.f5e4.5d2mc58jn.8de3aa74” such keys are dynamic
please correct me…

2

r is object not array so r[*] will result in MISSING.

You should do following

SELECT  * FROM `content`  as r
  WHERE REGEX_LIKE(meta().id , "user:+.*.sessions")
  AND "df" IN OBJECT_VALUES(r)[*].host;

OR

SELECT * FROM `content`  as r
  WHERE REGEX_LIKE(meta().id , "user:+.*.sessions")
  AND ANY h IN OBJECT_VALUES(r)  SATISFIES h.host = 'df' END;
3

thanks for reply . it is working fine.