Issues with JSON_EXTRACT in Presto for keys containing ' ' character

Question

I'm using Presto(0.163) to query data and am trying to extract fields from a json.

I have a json like the one given below, which is present in the column '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"
}

I'm unable to extract field 'Short Sleeves'. Below is the query i'm using:

Select JSON_EXTRACT(style_attributes,'$.attributes.Sleeve Length') as length from table;

The query fails with the following error- Invalid JSON path: '$.attributes.Sleeve Length'

For fields without ' '(space), query is running fine.

I tried to find the resolution in the Presto documentation, but with no success.

Answer 1

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

or

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

Answer 2

SELECT 
   tags -- It is column with Json string data
  ,json_extract(tags , '$.Brand') AS Brand
  ,json_extract(tags , '$.Portfolio') AS Portfolio
  ,cost
FROM
    TableName

Sample data for tags - {"Name": "pxyblob",  "Owner": "",  "Env": "prod",  "Service": "",  "Product": "",  "Portfolio": "OPSXYZ",  "Brand": "Limo",  "AssetProtectionLevel": "",  "ComponentInfo": ""}