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.