sql - Presto中包含 ' '字符的 key 的JSON_EXTRACT问题

Question

我正在使用Presto(0.163)查询数据，并尝试从json提取字段。

我有一个像下面给出的json，它出现在'style_attributes'列中:

"attributes": {
    "Brand Fit Name": "Regular Fit",
    "Fabric": "Cotton",
    "Fit": "Regular",
    "Neck or Collar": "Round Neck",
    "Occasion": "Casual",
    "Pattern": "Striped",
    "Sleeve Length": "Short Sleeves",
    "Tshirt Type": "T-shirt"
}

我无法提取“短袖”字段。
以下是我正在使用的查询:

从表中选择JSON_EXTRACT(style_attributes，'$。attributes.Sleeve Length')作为长度；

查询失败，并显示以下错误-无效的JSON路径:“$。attributes.Sleeve Length”

对于没有''(空格)的字段，查询运行良好。

我试图在Presto文档中找到分辨率，但是没有成功。

Answer 1

presto:default> select json_extract_scalar('{"attributes":{"Sleeve Length": "Short Sleeves"}}','$.attributes["Sleeve Length"]');
     _col0
---------------
 Short Sleeves

或者

presto:default> select json_extract_scalar('{"attributes":{"Sleeve Length": "Short Sleeves"}}','$["attributes"]["Sleeve Length"]');
     _col0
---------------
 Short Sleeves

JSON Function Changes

The :func:json_extract and :func:json_extract_scalar functions now support the square bracket syntax:

SELECT json_extract(json, '$.store[book]'); 
SELECT json_extract(json,'$.store["book name"]');

As part of this change, the set of characters allowed in a non-bracketed path segment has been restricted to alphanumeric, underscores and colons. Additionally, colons cannot be used in a un-quoted bracketed path segment. Use the new bracket syntax with quotes to match elements that contain special characters.

https://github.com/prestodb/presto/blob/c73359fe2173e01140b7d5f102b286e81c1ae4a8/presto-docs/src/main/sphinx/release/release-0.75.rst