如何查询在bigquery表中存储为字符串的json？ [英] How to query json stored as string in bigquery table?

问题描述

如何在bigquery表中查询存储为字符串的json？
我有一个表，列中的值（ subscriptions ）看起来像这样：

How to query json stored as string in bigquery table? I have a table where value in a column (subscriptions) looks like this:

{
    "data": [{
        "application_fee_percent": null,
        "canceled_at": null,
        "created": 1500476240,
        "items": {
            "data": [{
                "created": 1500476240,
                "id": "s4nQMWJn4P1Lg",
                "metadata": {},
                "object": "subscription_item",
                "plan": {
                    "amount": 3,
                    "created": 1494270926,
                    "currency": "usd",
                    "livemode": true,
                    "metadata": {
                        "currentlySelling": "true",
                        "features": "{\"shipping\": true,\"transactionFee\":0.00}",
                        "marketingFeatures": "[\"Unlimited products\"]"
                    },
                    "name": "Personal",
                    "object": "plan",
                    "statement_descriptor": null,
                    "trial_period_days": null
                },
                "quantity": 1
            }],
            "has_more": false,
            "object": "list",
            "total_count": 1,
            "url": "/v1/subscri3XwjA3"
        },
        "livemode": true,
        "metadata": {
            "test": "596f735756976"
        },
        "object": "suion",
        "quantity": 1
    }],
    "has_more": false,
    "object": "list",
    "total_count": 1,
    "url": "/v1/cutions"
}

如何选择 application_fee_percent ，特性和 marketingFeatures ？

对于创建，我尝试了
JSON_EXTRACT（订阅，$ .da

For created, I tried JSON_EXTRACT("subscriptions", "$.data[0].created") but it's returning null.

推荐答案

以下用于BgQuery SQL（请注意答案底部的注释！）

Below is for BgQuery SQL (please take attention to Note at the bottom of answer!)

#standardSQL
WITH yourTable AS (
  SELECT '''
    {
        "data": [{
            "application_fee_percent": null,
            "canceled_at": null,
            "created": 1500476240,
            "items": {
                "data": [{
                    "created": 1500476240,
                    "id": "s4nQMWJn4P1Lg",
                    "metadata": {},
                    "object": "subscription_item",
                    "plan": {
                        "amount": 2500,
                        "created": 1494270926,
                        "currency": "usd",
                        "id": "182463d635c6b6e43",
                        "interval": "month",
                        "interval_count": 1,
                        "livemode": true,
                        "metadata": {
                            "currentlySelling": "true",
                            "features": "{\'shipping\': true,\'transactionFee\':0.00}",
                            "marketingFeatures": "[\'Unlimited products\']"
                        },
                        "name": "Personal",
                        "object": "plan",
                        "statement_descriptor": null,
                        "trial_period_days": null
                    },
                    "quantity": 1
                }],
                "has_more": false,
                "object": "list",
                "total_count": 1,
                "url": "/v1/subscri3XwjA3"
            },
            "livemode": true,
            "metadata": {
                "test": "596f735630012756976"
            },
            "object": "subscription",
            "quantity": 1,
            "start": 1500476240,
            "status": "trialing",
            "tax_percent": 0.0,
            "trial_end": 1501685839,
            "trial_start": 1500476240
        }],
        "has_more": false,
        "object": "list",
        "total_count": 1,
        "url": "/v1/cutions"
    }
  ''' AS subscriptions
)
SELECT 
  JSON_EXTRACT_SCALAR(subscriptions, "$.data[0].application_fee_percent") AS application_fee_percent,
  JSON_EXTRACT_SCALAR(subscriptions, "$.data[0].created") AS created,
  JSON_EXTRACT_SCALAR(subscriptions, "$.data[0].items.data[0].plan.metadata.currentlySelling") AS currentlySelling,
  JSON_EXTRACT_SCALAR(subscriptions, "$.data[0].items.data[0].plan.metadata.features") AS features,
  JSON_EXTRACT_SCALAR(subscriptions, "$.data[0].items.data[0].plan.metadata.marketingFeatures") AS marketingFeatures
FROM yourTable

结果如预期

The result is as expected

application_fee_percent created     currentlySelling  features marketingFeatures      
null                    1500476240  true              {'shipping': true,'transactionFee':0.00}  ['Unlimited products']   

请注意：您的JSON对于功能和 marketingFeatures - 它们包含双引号内的双引号 - 因此，您可以看到我在示例数据中更改了这一点 - 但您需要修复生成它的那部分应用程序

Please note: your JSON is not valid for features and marketingFeatures - they consists double quotes inside double quotes - so as you can see I changed this in your example data - but you need to fix that part of your application that produces it

添加了原始字符串使用示例：
$ b

Added example of raw string use:

#standardSQL
WITH YourTable AS (
  SELECT R"""{
    "features": "{\"shipping\": true,\"productLimit\":5,\"discounts\": false,\"customDomain\": false, \"imageLimit\": 100,\"transactionFee\":0.03} "
  }""" AS subscriptions
)
SELECT
  JSON_EXTRACT_SCALAR(subscriptions, '$.features') AS features
FROM YourTable

结果是

result is

features     
{"shipping": true,"productLimit":5,"discounts": false,"customDomain": false, "imageLimit": 100,"transactionFee":0.03}

这篇关于如何查询在bigquery表中存储为字符串的json？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！