6

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:"

Manfred Moser
  • 29,539
  • 13
  • 92
  • 123
l8Again
  • 263
  • 3
  • 9

1 Answers1

12

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).
Martin Traverso
  • 4,731
  • 15
  • 24
  • same concept applies for "-" hyphen or dash character as well. i was facing similar issue in AWS Athena which relies on presto json functions. – shamanth Gowdra Shankaramurthy Nov 30 '17 at 20:51
  • Just in case you're wondering, the array element like this to access nested elements such as `{"foo":{"~bar":1}}` 'would look like this: `$["foo"]["~bar"]` – nbarraille Dec 17 '18 at 12:16