How to access a json field with "~" in the field name with Presto JSON functions

Question

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:

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

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

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

Error given is "Invalid JSON path:"

Answer 1

The correct form for that JSON path is: '$["~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 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).