Using Postgres and JSON Functions and Operators to parse JSON fields

[Joshua_Moskovit]

Any SQL functionality that is native to your dialect can be leveraged in the sql: parameters within LookML, or as derived tables. In this case we will cover Postgres’ JSON functions and operators

The ->, ->>, and json_object_keys functions/operators in PostgreSQL allow you to extract values associated with keys in a JSON string.

If we have this JSON string:

{  
   "text":"I need a gift for my husband's birthday.  He loves nice electronics and is in the process of upgrading his wardrobe.  I think something special for his wardrobe would be great, a sportscoat or  some shoes. I am a little stumped on something that would really surprise him or stand out and could use your help! Sizes:\nShirts-15.5/37\nPant-36x36\nSportscoat-46L\nThank you!",
   "notes":{  
      "-JggWN6uFGBViAGDN0_i":{  
         "user":"simplelogin:72",
         "message":"Let us know what you think of these initial picks for husband's birthday!",
         "timestamp":1422381122078
      },
      "-JhBz_UgDxshvgpCC0SH":{  
         "user":"simplelogin:72",
         "message":"Nikhila, did you get a chance to peek at our ideas? We'd love to hear your feedback!",
         "timestamp":1422925912002
      },
      "-JhCoFPGIdTYZ3_gpp4F":{  
         "user":"simplelogin:100",
         "message":"So sorry Alex, this went to my spam and I had been eagerly awaiting the email! He just got a Fitbit for Christmas and has great headphones. I love both those boots and the blazer! There is one thing I wanted to also see if you could check out. He is an awesome cook and loves experimenting in the kitchen. Is there a good kitchen gadget (simple as awesome knives to something more creative) that you might be able to suggest? I think I will end up choosing from one of these 3 things!\nThank you!!",
         "timestamp":1422939722307
      },
      "-JhCohhkK91nbQQe-Otg":{  
         "user":"simplelogin:100",
         "message":"I forgot to add that he is a size 14 shoe! Does this company make those boots in that size?",
         "timestamp":1422939842334
      },
      "-JhHfj8UUvfp6lgpm4Gt":{  
         "user":"simplelogin:72",
         "message":"Thanks for the feedback! Unfortunately the chukkas we originally pulled only go up to a size 13, however we added a few more pairs that feel very similar that are available in a size 14. We've also included a few picks for fun kitchen items that your master chef can try out at home.",
         "timestamp":1423021372349
      },
      "-JhQicYTaZKRlCQ-iY2L":{  
         "user":"simplelogin:100",
         "message":"Hi Alex,\nThanks for all the suggestions. He ended up choosing his own gift, which actually were just waterproof boots. I also have an anniversary coming up in May so I will definitely visit Scratch again for your help!!!",
         "timestamp":1423173139988
      }
   },
   "title":"Husband's birthday present",
   "sfdcId":"006o0000007BdemAAC",
   "status":"options_available",
   "shopper":"simplelogin:72",
   "deadline":"2001/02/13",
   "maxPrice":"200",
   "shopperResponse":{  
      "user":"simplelogin:72",
      "message":""
   },
   "currentChatMessage":""
}

We can create the following Postgres SQL query:

SELECT *
   , requests.request -> 'notes' -> json_object_keys((requests.request ->> 'notes')::json) ->> 'message' AS message
FROM requests

which does the following:

1. Finds the “notes” key;
2. Within the sub-JSON object, “notes,” extracts the variable keys for each message;
3. For each variable message key, gets the message text;
4. De-nests the string of messages stored in a jsonb field.

In LookML you could define a dimension like so:

  dimension: notes {
  sql: ${TABLE}.request -> 'notes';;
}

  dimension: message {
  sql: json_object_keys((${TABLE}.request->> 'notes')::json) ->> 'message' ;;
}

And it would extract the appropriate values from the JSON blob as dimension values.

[Anthony_Maggio]

@Joshua_Moskovit Thanks for this post. It would be nice to have better support (ie. auto-detection) for JSON fields built in. If you don’t go through and manually update your dimensions to tell Looker that a field contains JSON data, it will break an Explore that references the field. It seems like Looker could at least detect the field contains JSON and cast the data as varchar out of the box.

[Joshua_Moskovit]

Thanks @Anthony_Maggio!

I’ve also passed along your feedback on auto-detection of JSON fields to the product team.

[Andrew_Kraemer]

Could you add the output of the postgres sql query?

[jmarshall11]

The above was very helpful and I’d like to add a way I was able to parse a JSON list field that varies in length with LookML.

Problem:
Field to parse is jsonb list:

Solution:
Create a PDT based on the result jsonb_array_elements and join this PDT back to the model with a one_to_many relationship:

This results in the following:

Hopefully this is helpful to to others parsing json stored in a Postgres db