如何使用Presto JSON函数在字段名称中使用“〜”访问json字段

I have a "~" in my json fields, such as "~id". Using Presto 0.75, I am unable to access such fields. Following is what I have tried so far without success:

我的json字段中有一个“〜”，例如“~id”。使用Presto 0.75，我无法访问这些字段。以下是我迄今为止没有成功的尝试：

SELECT json_extract_scalar('{"id":"1","table":"test"}', '$.table'); // This works

SELECT json_extract_scalar（'{“id”：“1”，“table”：“test”}'，'$ .table'）; //这很有效

SELECT json_extract_scalar('{"id":"1","~table":"test"}', '$.[\"~table\"]'); // Doesn't work

SELECT json_extract_scalar（'{“id”：“1”，“〜table”：“test”}'，'$。[\“~table \”]'）; //不起作用

SELECT json_extract_scalar('{"id":"1","~table":"test"}', '$.[\~table]'); // Doesn't work

SELECT json_extract_scalar（'{“id”：“1”，“〜table”：“test”}'，'$。[\〜table]'）; //不起作用

Error given is "Invalid JSON path:"

给出的错误是“无效的JSON路径：”

1 个解决方案

#1

The correct form for that JSON path is: '$["~table"]':

该JSON路径的正确形式是：'$ [“~table”]'：

presto> SELECT json_extract_scalar('{"id":"1","~table":"test"}', '$["~table"]');

 _col0 
-------
 test  
(1 row)

Here are some facts to help you understand why the alternatives you tried don't work:

以下是一些事实可以帮助您理解为什么您尝试的替代方案不起作用：

The JSON path expression is represented with a SQL string. The only character that needs escaping is the string delimiter (i.e., single quote), and the way you to do it is with another single quote. For example: 'don''t' is the SQL string literal for don't. Double quotes within a SQL string literal do not need to be escaped.
JSON路径表达式用SQL字符串表示。需要转义的唯一字符是字符串分隔符（即单引号），而您执行此操作的方式是使用另一个单引号。例如：'don''t'不是SQL字符串文字。 SQL字符串文字中的双引号不需要转义。
JSON path expressions support two forms for accessing attributes: field vs array element access. If you have an attribute named "foo", you can access it either with '$["foo"]' or '$.foo'. The field access syntax only works for names that are valid identifiers (alphanumeric and underscores).
JSON路径表达式支持两种访问属性的形式：字段与数组元素访问。如果你有一个名为“foo”的属性，你可以使用'$ [“foo”]'或'$ .foo'来访问它。字段访问语法仅适用于有效标识符（字母数字和下划线）的名称。

#1