我需要对这个json做些什么才能把它转换成一个表？我需要在json本身中执行查询吗？
下面是bigquery标准sql

# standardSQL

SELECT * EXCEPT(data, cart_item), 
  JSON_EXTRACT(data, '$.status') AS status, 
  JSON_EXTRACT(data, '$.storeName') AS storeName,
  JSON_EXTRACT(cart_item, '$.qty') AS qty,
  JSON_EXTRACT(cart_item, '$.description') AS description,
  JSON_EXTRACT(cart_item, '$.productName') AS productName,
  JSON_EXTRACT(cart_item, '$.price') AS price,
  JSON_EXTRACT(cart_item, '$.productId') AS productId
FROM `project.dataset.table`,
UNNEST(JSON_EXTRACT_ARRAY(data, '$.cart')) cart_item

如果要应用于您问题中的样本数据，请参见下面的示例

# standardSQL

WITH `project.dataset.table` AS (
  SELECT 1 order_id, '''
{
    "cart": [{
        "qty": 1,
        "description": "Sprite 1 L",
        "productName": "Sprite 1 Liter",
        "price": 1.99,
        "productId": 9
    },{
        "qty": 2,
        "description": "Fanta 1 L",
        "productName": "Fanta 1 Liter",
        "price": 1.99,
        "productId": 10
    }],
    "storeName": "My awesome shop",
    "status": 5,
    "timestamp": {
        "_seconds": 1590713204,
        "_nanoseconds": 916000000
    }
}  
'''  data 
)
SELECT * EXCEPT(data, cart_item), 
  JSON_EXTRACT(data, '$.status') AS status, 
  JSON_EXTRACT(data, '$.storeName') AS storeName,
  JSON_EXTRACT(cart_item, '$.qty') AS qty,
  JSON_EXTRACT(cart_item, '$.description') AS description,
  JSON_EXTRACT(cart_item, '$.productName') AS productName,
  JSON_EXTRACT(cart_item, '$.price') AS price,
  JSON_EXTRACT(cart_item, '$.productId') AS productId
FROM `project.dataset.table`,
UNNEST(JSON_EXTRACT_ARRAY(data, '$.cart')) cart_item

结果是

Row order_id    status  storeName           qty     description     productName         price   productId    
1   1           5       "My awesome shop"   1       "Sprite 1 L"    "Sprite 1 Liter"    1.99    9    
2   1           5       "My awesome shop"   2       "Fanta 1 L"     "Fanta 1 Liter"     1.99    10

您可以使用json函数

CrEATE Table products (id Integer,attribs_json JSON );

INSERT INTO products VALUES (1,'{
    "cart": [{
        "qty": 1,
        "description": "Sprite 1 L",
        "productName": "Sprite 1 Liter",
        "price": 1.99,
        "productId": 9
    }],
    "storeName": "My awesome shop",
    "status": 5,
    "timestamp": {
        "_seconds": 1590713204,
        "_nanoseconds": 916000000
    }
}');

select * from products where attribs_json->"$.status" 
= 5 AND attribs_json->"$.storeName" 
= 'My awesome shop';

id | attribs_json                                                                                                                                                                                                                      
-: | :---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 1 | {"cart": [{"qty": 1, "price": 1.99, "productId": 9, "description": "Sprite 1 L", "productName": "Sprite 1 Liter"}], "status": 5, "storeName": "My awesome shop", "timestamp": {"_seconds": 1590713204, "_nanoseconds": 916000000}}

db<>在这里摆弄

select attribs_json->"$.storeName",attribs_json->"$.status",attribs_json->"$.cart[0].qty" from products where attribs_json->"$.status" 
= 5 AND attribs_json->"$.storeName" 
= 'My awesome shop';

attribs_json->"$.storeName" | attribs_json->"$.status" | attribs_json->"$.cart[0].qty"
:-------------------------- | :----------------------- | :----------------------------
"My awesome shop"           | 5                        | 1

db<>在这里摆弄
MySQL5.7及更高版本也有json提取。
最后，这是只在最后的文本，所以您也可以使用regexp或rlike
要再次将jaso传输到行，可以使用json\u表

您必须做的是从json数据中提取如下值：select。。。。。。。其中data->'$.storename'=“我的超级商店”和data->'$.status'=5
从“购物车”中提取或´“timestamp”键将为您提供一个需要进一步提取以获取数据的json对象。我希望它能帮助你你可能想看看mysql文档(https://dev.mysql.com/doc/refman/8.0/en/json.html)或者https://www.mysqltutorial.org/mysql-json/.

您可以在where子句中使用unnest来访问cart的列，在where子句中使用json函数来过滤所需的行。您需要注意访问json根或数组 cart ; json_data 以及 cart_items 在下面的例子中（顺便说一下，在你的例子中 shopName 不存在但是 storeName 是的）。

WITH
  `myapp-1a602.firestore_orders.orders_raw_changelog` AS (
  SELECT
    '{"cart": [{"qty": 1,"description": "Sprite 1 L","productName": "Sprite 1 Liter","price": 1.99,"productId": 9}, {"qty": 11,"description": "Sprite 11 L","productName": "Sprite 11 Liter","price": 11.99,"productId": 19}],"storeName": "My awesome shop","status": 5,"timestamp": {"_seconds": 1590713204,"_nanoseconds": 916000000}}' json_data )
SELECT
  JSON_EXTRACT(json_data, '$.status') AS status,
  JSON_EXTRACT(json_data, '$.storeName') AS storeName,
  JSON_EXTRACT(cart_items, '$.productName') AS product,
  JSON_EXTRACT_SCALAR(cart_items, '$.qty') AS qty
FROM
  `myapp-1a602.firestore_orders.orders_raw_changelog`,
  UNNEST(JSON_EXTRACT_ARRAY(json_data, '$.cart')) AS cart_items
WHERE
  JSON_EXTRACT(json_data,'$.storeName') like "\"My awesome shop\"" AND 
  CAST(JSON_EXTRACT_SCALAR(json_data,'$.status') AS NUMERIC) = 5