json - 如何从 couchbase 查询深层嵌套的 json 数组?
问题描述
如何从 couchbase 查询深层嵌套的 json 数组?我在 couchbase 存储桶中有以下文件。我需要查询以列出所有具有“android.permission.BATTERY_STATS”权限的应用程序
如何查询以列出所有具有嵌套 json 数组权限的应用程序?
我的 Json 文件,
文件: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
}
文件: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
}
解决方案
正如我在评论中所说,这是一个棘手的问题。我将首先尝试简化您的文档,然后给出我想出的答案。
您有两个文档,其中包含一个带有权限数组的嵌套对象。每个嵌套对象都有一个(可能)不同的名称。所以,假设我们有两个像这样的简单文档:
id: doc1
{
"foo": {
"permissions": [
"android.permission.ACCESS_COARSE_LOCATION",
"android.permission.BATTERY_STATS"
]
}
}
id: doc2
{
"bar": {
"permissions": [
"android.permission.ACCESS_FINE_LOCATION"
]
}
}
第一个有一个“foo”嵌套对象,第二个有一个“bar”嵌套对象,但两个嵌套对象都有一个“permissions”数组。您想查找所有具有“android.permission.BATTERY_STATS”权限的文档。
我查看了 N1QL 文档以获取任何可能有用的信息,我特别查看了对象函数部分。有一个函数OBJECT_UNWRAP
可以解决这个问题。来自文档:“此函数使您能够在不知道名称-值对中的名称的情况下打开对象。”
所以,如果我只是简单地打开上面的文件,那么我基本上可以丢弃“foo”和“bar”部分。
SELECT META(b).id, OBJECT_UNWRAP(b).permissions
FROM sstbucket b
如有必要,您可以展开更深的嵌套对象,但我试图保持简单。
该查询的结果将是:
[
{
"id": "doc1",
"permissions": [
"android.permission.ACCESS_COARSE_LOCATION",
"android.permission.BATTERY_STATS"
]
},
{
"id": "doc2",
"permissions": [
"android.permission.ACCESS_FINE_LOCATION"
]
}
]
现在,ANY/SATISFIES
查找文档是一个简单的语句:
SELECT META(b).id
FROM sstbucket b
WHERE ANY p IN OBJECT_UNWRAP(b).permissions SATISFIES p == 'android.permission.BATTERY_STATS' END;
哪个会返回
[
{
"id": "doc1"
}
]
所以,这行得通。我不确定的是如何为这个特定的查询创建一个合适的索引。我创建了一个主索引只是为了让它工作 ( CREATE PRIMARY INDEX ON sstbucket
),但这不会很好地执行。
推荐阅读
- hive - 飞路分贝 | HIVE 的 Liquibase 等效项
- apache - Apache 通配符 SSL 配置以及同一域的组织 SSL
- html - Bootstrap 3 col 上的填充似乎无法删除
- ios - 我可以在 Swift 中以编程方式阻止在指定时间(夜间)接收 iOS 推送通知吗
- r - 错误:系统在计算上是奇异的 -> 使用 pgmm 进行系统 GMM 回归
- ios - 如何实现对 tableview 单元格的 3D 触摸以使用 Swift 4 预览该单元格?
- email - 如果服务器停机,发送或接收电子邮件
- apostrophe-cms - 提交权限为“假”的作品未更新到草稿模式。仅在实时模式下可用
- java - 用于 Mac 的预编译 opencv 库
- sqlite - 是否可以在同一个项目中使用 sqlite-net-pcl 和 Akavache 的纯功能?