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:
- Finds the “notes” key;
- Within the sub-JSON object, “notes,” extracts the variable keys for each message;
- For each variable message key, gets the message text;
- 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.
