CODEWITHSUNDEEP
jsonnestedcouchbasesql++
SST
SST

Reputation: 2144

How to query deep nested json array from couchbase?

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
}

Upvotes: 1

Views: 1409

Answers (2)

vsr
vsr

Reputation: 7414

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

Upvotes: 1

Matthew Groves
Matthew Groves

Reputation: 26169

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.

Upvotes: 1

Related Questions