How to query deep nested json value from couchbase?

Question

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"
  }
}

Be sure to test that your code correctly handles version components greater than 9. For example, make sure you get the expected results when comparing 1.2.3 against 1.10.0. — dnault
– dnault, Commented Oct 30, 2018 at 20:06

vsr · Accepted Answer · 2018-10-30 13:21:01Z

3

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");

answered Oct 30, 2018 at 13:21

vsr

7,4441 gold badge13 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

deniswsrosa · Accepted Answer · 2018-10-29 13:37:22Z

1

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

answered Oct 29, 2018 at 13:37

deniswsrosa

2,4681 gold badge18 silver badges28 bronze badges

2 Comments

SST Over a year ago

I didnt get any response while running this query. It reurns a rows when the value object is array, otherwise it returns empty.

SST Over a year ago

How to query nested entities is object??

Johan Larson · Accepted Answer · 2018-10-30 08:56:51Z

1

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.

answered Oct 30, 2018 at 8:56

Johan Larson

1,9001 gold badge11 silver badges14 bronze badges

3 Comments

SST Over a year ago

No, the above query is not working.. :( I need to select the row which contains the appversion=3.4.1. But I couldn't query "select * from mybucket where appversion=3.4.1" because the appversion under value object (depth nested object). I want to get that row using n1ql query. Is there any way to query the nested object?

Johan Larson Over a year ago

It has to be value.appversion . The dot is what reaches down into the "value" subobject.

SST Over a year ago

yes, I queried with value.appversion but getting error.. { "code": 3000, "msg": "syntax error - at value", "query_from_user": "SELECT * FROM mybucket WHERE value.appversion=\"3.4.1\";" }

Collectives™ on Stack Overflow

How to query deep nested json value from couchbase?

3 Answers 3

Comments

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related