json提取嵌套字符串trait

omqzjyyz 于 2023-03-09 发布在其他

关注(0)|答案(1)|浏览(130)

这是JSON语句的一部分。

{
  "customer": {
    "spendingHold": false,
    "createdAt": "2023-03-08T00:00:00.000Z",
    "addresses": [
      {
        "country": "USA",
        "preferences": {
          "contact": {
            "allowed": {
              "mail": true
            }
          }
        },
        "city": "Place",
        "postalCode": "11111",
        "street1": "123 Circle",
        "street2": null,
        "id": "1234567890",
        "type": "home",
        "region": "ST",
        "primary": true
      }
    ],
      "contact": {
        "allowed": {
          "times": null,
          "transactional": true
        }
      }
    },
    "creationSource": "created",
}
}

我可以提取客户和creationSource

SELECT
JSON_EXTRACT_SCALAR(json, "$.customer.spendingHold") AS spending_hold,
FROM dataset

但我很难尝试获得像地址这样的特征，以及像国家、偏好、联系方式等特征。
所需的输出应如下所示：
| 支出保留|创建时间|乡村|邮件|城市|
| - ------|- ------|- ------|- ------|- ------|
| 假的|2023年3月8日上午00时00分00秒|美国|真|地点|

JSON

来源：https://stackoverflow.com/questions/75675505/json-extract-string-traits-that-are-nested

1条答案

按热度按时间

cbwuti441#

您可以考虑以下查询

WITH dataset AS (
  SELECT '''
  -- put your json string here.
  ''' json
)
SELECT JSON_VALUE(json, "$.customer.spendingHold") AS spending_hold,
       JSON_VALUE(json, "$.customer.createdAt") AS createdAt,
       JSON_VALUE(address, '$.country') AS country,
       JSON_VALUE(address, '$.preferences.contact.allowed.mail') AS mail,
       JSON_VALUE(address, '$.city') AS city
  FROM dataset, UNNEST(JSON_QUERY_ARRAY(json, '$.customer.addresses')) address;

查询结果

另请参见-JSON functions

赞(0）回复(0）举报 2023-03-09

我来回答

json提取嵌套字符串trait

1条答案

相关问题

热门标签

最新问答