How to query json stored as string in bigquery table?

Question

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

How can I select application_fee_percent, features, and marketingFeatures?

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

Part of the problem is that the JSON that you shared is not valid (try pasting it into jsonlint.com), so it won't be possible to use JSON_EXTRACT with it unless maybe there was a mistake in transcription. — Elliott Brossard
That is my bad. I have a big value and I tried to make it smaller for the post and I messed up. Updating it and the new json is valid. — BI Architect

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2017-07-19T20:01:01

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']

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

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}

How to query json stored as string in bigquery table?

1 Answers