How to query deep nested json array from couchbase?

Question

How to query deep nested json array from couchbase? I have the following documents in the couchbase bucket. I need to query to list all apps who have Permissions "android.permission.BATTERY_STATS"

How to query to list all apps with permissions from nested json array?

My Json Documents,

Document:1

{
  "data": {
    "com.facebook.katana": {
      "studioId": "Facebook",
      "screenshotUrls": [
        "https://lh3.googleusercontent.com/JcPdPqplBxgG6dEQuxvuhO4jvE64AzxOCGWe8w55dMMeXU4rZs2MwpfGQTWvv6QR-g",
        "https://lh3.googleusercontent.com/w0kSYY7jlPjGDd3KEVgDTpzUf4k67G7rfELOf6qj1SSC7n6Ege44vp8QkeX57ZM6bFU"
      ],
      "primaryCategoryName": "Social",
      "studioName": "Facebook",
      "description": "Keeping up with friends is faster and easier than ever. Share updates and photos, engage with friends and Pages, and stay connected to communities important to you"
      "starRatings": {
        "1": 9706642,
        "2": 3384344,
        "3": 7224416,
        "4": 12323358,
        "5": 49438051
      },
      "numDownloads": "1,000,000,000+ downloads",
      "price": 0,
      "permissions": [
        "android.permission.ACCESS_COARSE_LOCATION",
        "android.permission.ACCESS_FINE_LOCATION",
        "android.permission.ACCESS_NETWORK_STATE",
        "android.permission.ACCESS_WIFI_STATE",
        "android.permission.AUTHENTICATE_ACCOUNTS",
        "android.permission.BATTERY_STATS",
        "android.permission.BLUETOOTH",
        "android.permission.READ_PHONE_STATE",
        "android.permission.READ_PROFILE",
        "android.permission.READ_SMS",
        "android.permission.READ_SYNC_SETTINGS",
        "com.nokia.pushnotifications.permission.RECEIVE",
        "com.sec.android.provider.badge.permission.READ",
        "com.sec.android.provider.badge.permission.WRITE",
        "com.sonyericsson.home.permission.BROADCAST_BADGE"
      ],
      "appId": "com.facebook.katana",
      "userRatingCount": 82076811,
      "currency": "USD",
      "iconUrl": "https://lh3.googleusercontent.com/ZZPdzvlpK9r_Df9C3M7j1rNRi7hhHRvPhlklJ3lfi5jk86Jd1s0Y5wcQ1QgbVaAP5Q=w100",
      "releaseDate": "Nov 14, 2018",
      "appName": "Facebook",
      "studioUrl": "https://www.facebook.com/facebook",
      "hasInAppPurchases": 1,
      "bundleId": "com.facebook.katana",
      "version": "198.0.0.53.101",
      "commentCount": 22211936,
      "fileSizeBytes": 58044343,
      "formattedPrice": "",
      "categoryIds": [
        "APPLICATION",
        "SOCIAL"
      ],
      "tagline": "Find friends, watch live videos, play games & save photos in your social network",
      "averageUserRating": 4.0770621299744,
      "primaryCategoryId": "SOCIAL",
      "videoScreenUrl": "https://lh4.ggpht.com/3RG_Y8JPK0Hcyui9OcapiONP_aDWKTRZ50wqZW_wbyOF0FamAYEYZfMTW9Cs1OT1kA"
    }
  },
  "response_msec": 11,
  "status": 200
}

Document:2

{
    "data": {
    "com.whatsapp": {
      "studioId": "WhatsApp Inc.",
      "screenshotUrls": [
        "https://lh3.googleusercontent.com/MMue08byixTw74ST_VkNQDUUJBgVEbjNHDYLhIuHmYhMIMJIp3KjVlnhhqZQOZUtNt8",
        "https://lh3.googleusercontent.com/foFmwvVGIwWWXJIukN7png18lFjFgbw3K7BqIm8G-jsFgSTVtkCa-dDkFApUzbvzIvbe"
      ],
      "primaryCategoryName": "Communication",
      "studioName": "WhatsApp Inc.",
      "description": "WhatsApp Messenger is a FREE messaging app available for Android and other smartphones.
      "starRatings": {
        "1": 4713598,
        "2": 1917919,
        "3": 4962745,
        "4": 11307648,
        "5": 55392894
      },
      "numDownloads": "1,000,000,000+ downloads",
      "price": 0,
      "permissions": [
        "android.permission.ACCESS_COARSE_LOCATION",
        "android.permission.ACCESS_FINE_LOCATION",
        "android.permission.ACCESS_NETWORK_STATE",
        "android.permission.ACCESS_WIFI_STATE",
        "android.permission.AUTHENTICATE_ACCOUNTS",
        "android.permission.BLUETOOTH",
        "android.permission.BROADCAST_STICKY",
        "android.permission.CAMERA",
        "android.permission.CHANGE_WIFI_STATE",
        "android.permission.GET_ACCOUNTS",
        "android.permission.GET_TASKS",
        "android.permission.INSTALL_SHORTCUT",
        "android.permission.INTERNET",
        "android.permission.MANAGE_ACCOUNTS",
        "com.whatsapp.permission.REGISTRATION",
        "com.whatsapp.permission.VOIP_CALL",
        "com.whatsapp.sticker.READ"
      ],
      "appId": "com.whatsapp",
      "userRatingCount": 78294804,
      "currency": "USD",
      "iconUrl": "https://lh6.ggpht.com/mp86vbELnqLi2FzvhiKdPX31_oiTRLNyeK8x4IIrbF5eD1D5RdnVwjQP0hwMNR_JdA=w100",
      "releaseDate": "Nov 5, 2018",
      "appName": "WhatsApp Messenger",
      "studioUrl": "http://www.whatsapp.com/",
      "bundleId": "com.whatsapp",
      "version": "2.18.341",
      "commentCount": 19763316,
      "fileSizeBytes": 23857699,
      "formattedPrice": "",
      "categoryIds": [
        "APPLICATION",
        "COMMUNICATION"
      ],
      "tagline": "Simple. Personal. Secure.",
      "averageUserRating": 4.4145045280457,
      "primaryCategoryId": "COMMUNICATION",
      "videoScreenUrl": "https://lh3.ggpht.com/aZrXAunkovhf0630Ykz1A7h2rzFX_dErd6fRiB7fNKU_DkNtetTquEra1bjc3sR2kLs"
    }
  },
  "response_msec": 15,
  "status": 200
}

This is going to be tricky if because permissions isn't in the same field for each document. In the first it's 'com.facebook.katana'.permissions and in the second it's 'com.whatsapp'.permissions. Is there any way you can remodel this data to make the permissions field the same from document to document? — Matthew Groves
– Matthew Groves, Commented Feb 12, 2019 at 14:47
No, I get the respose from REST API and just stored what I get. So how can I query list the apps which have 'android.permission.whatever'? — SST
– SST, Commented Feb 13, 2019 at 12:43

Matthew Groves · Accepted Answer · 2019-02-13 16:26:28Z

As I say in the comment, this is a tricky one. I'm going to try to simplify your docs first, and then give an answer that I came up with.

You have two docs, which contain a nested object with a permissions array. Each nested object has a (potentially) different name. So, let's assume we have two simple docs like this:

id: doc1
{
  "foo": {
    "permissions": [
      "android.permission.ACCESS_COARSE_LOCATION",
      "android.permission.BATTERY_STATS"
    ]
  }
}

id: doc2
{
  "bar": {
    "permissions": [
      "android.permission.ACCESS_FINE_LOCATION"
    ]
  }
}

The first one has a "foo" nested object, the second has a "bar" nested object, but both nested objects have a "permissions" array. You want to find all the documents that have a permission of "android.permission.BATTERY_STATS".

I checked out the N1QL docs for anything that might be helpful, and I especially checked out the Object Functions section. There's a function called OBJECT_UNWRAP that might do the trick. From the docs: "This function enables you to unwrap an object without knowing the name in the name-value pair."

So, if I simply unwrap the above documents, then I can basically discard the "foo" and the "bar" parts.

SELECT META(b).id, OBJECT_UNWRAP(b).permissions
FROM sstbucket b

You can put unwrap a deeper nested object if necessary, but I'm trying to keep this simple.

The results of that query would be:

[
  {
    "id": "doc1",
    "permissions": [
      "android.permission.ACCESS_COARSE_LOCATION",
      "android.permission.BATTERY_STATS"
    ]
  },
  {
    "id": "doc2",
    "permissions": [
      "android.permission.ACCESS_FINE_LOCATION"
    ]
  }
]

And now, it's a simple ANY/SATISFIES statement to find the document:

SELECT META(b).id
FROM sstbucket b
WHERE ANY p IN OBJECT_UNWRAP(b).permissions SATISFIES p == 'android.permission.BATTERY_STATS' END;

Which would return

[
  {
    "id": "doc1"
  }
]

So, that works. What I don't know for sure is how to create a proper index for this particular query. I created a primary index just to make it work (CREATE PRIMARY INDEX ON sstbucket), but that's not going to perform very well.

Yes, I have primary index but this query returns empty result.
You will need to customize it for your documents (like OBJECT_UNWRAP(b.data) or similar)

vsr · Accepted Answer · 2019-02-13 17:40:19Z

You can use OBJECT functions (https://docs.couchbase.com/server/6.0/n1ql/n1ql-language-reference/objectfun.html) and Array indexing.

If you need document ID or whole document.

CREATE INDEX ix1 ON default ( DISTINCT ARRAY  (DISTINCT ARRAY permision
                                               FOR permision IN app.permissions END)
                                       FOR app IN OBJECT_VALUES(data) END);
SELECT META(d).id FROM default AS d
WHERE ANY app IN OBJECT_VALUES(d.data)
      SATISFIES (ANY permision IN app.permissions
                 SATISFIES permision = "android.permission.BATTERY_STATS"
                 END)
      END;

If you need only appId and see if it uses covering index.

CREATE INDEX ix2 ON default ( ALL ARRAY  (ALL ARRAY [permision, app.appId]
                                               FOR permision IN app.permissions END)
                                       FOR app IN OBJECT_VALUES(data) END);

SELECT [permision, app.appId][1] AS appId  FROM default AS d
UNNEST OBJECT_VALUES(d.data) AS app
UNNEST app.permissions AS permision
WWHERE [permision, app.appId] >= ["android.permission.BATTERY_STATS"] AND
        [permision, app.appId] < [SUCCESSOR("android.permission.BATTERY_STATS")] ;

Collectives™ on Stack Overflow

How to query deep nested json array from couchbase?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related