CODEWITHSUNDEEP
jsonregexnestedcouchbasesql++
SST
SST

Reputation: 2144

How to query deep nested json value from couchbase?

How to query deep nested json value from couchbase? I have the following documents in the couchbase bucket. I need to query appversion>3.2.1 OR appversion <3.3.0 OR appversion=3.4.1.

How to query these values from nested json?

My Json Documents,

Document 1: 

com.whatsapp_1

{
  "doc-type": "App-Metadata",
  "bundleid": "com.whatsapp",
  "value": {
    "appId": "com.whatsapp",
    "appName": "WhatsApp Messenger",
    "primaryCategoryName": "Communication"
  }
}

Document 2:

com.whatsapp_2

 {
  "doc-type": "App-Lookalike",
  "bundleid": "com.whatsapp",
  "value": {
    "com.facebook.orca": 476664,
    "org.telegram.messenger.erick.lite": 423132,
    "com.viber.voip": 286410,
    "messenger.free.video.call.chat": 232830,
    "com.facebook.katana": 223000,
    "com.wChatMessenger_6210995": 219960,
    "com.facebook.talk": 187884
  }
}

Document 3:

com.whatsapp_3

{
  "doc-type": "Internal-Metadata",
  "bundleid": "com.whatsapp",
  "value": {
    "appversion": "3.4.1"
  }
}

Upvotes: 1

Views: 2182

Answers (3)

vsr
vsr

Reputation: 7414

value is reserved keyword, you need to use back-ticks around it.

SELECT *
FROM sampleBucket
WHERE `doc-type` = 'Internal-Metadata' AND
      (`value`.appversion>"3.2.1" OR 
      `value`.appversion <"3.3.0" OR 
      `value`.appversion="3.4.1");

Upvotes: 3

Johan Larson
Johan Larson

Reputation: 1890

I'm not quite sure what you want, but if you want a query that only returns document 3, this query should do it.

SELECT *
FROM sampleBucket
WHERE value.appversion>"3.2.1" OR value.appversion <"3.3.0" OR value.appversion="3.4.1"

This should return only the third document. The query also assumes all app versions are of the from x.y.z where x, y, and z are single-digit numbers.

If that's not the result you are looking for, please explain more precisely what you want.

Upvotes: 1

deniswsrosa
deniswsrosa

Reputation: 2460

To query nested entities you should use the unnest keyword: https://dzone.com/articles/nesting-and-unnesting-in-couchbase-n1ql

In your case, it will be something similar to:

select t.* from mybucket t UNNEST `t.value` v where t.doc-type = 'Internal-Metadata' and v.appversion = '3.2.1'

As you are app versions are String, you should use the replace function to remove "." and then convert it to int before the comparison

https://docs.couchbase.com/server/5.5/n1ql/n1ql-language-reference/stringfun.html#fn-str-replace

Upvotes: 1

Related Questions