Getting started with Looker

Best Practices

Documentation

Community

Training

Certification

Articles in this section

See more

Using Postgres and JSON Functions and Operators to Parse JSON Fields

Avatar
Joshua Moskovitz
  • Updated
Follow

Any SQL functionality that is native to your dialect can be leveraged in the sql parameter within fields or derived tables. In this case we will demonstrate how to leverage Postgres' JSON functions and operators to parse JSON fields within the sql parameter.

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

  • -> returns any JSON array element, or JSON object field.
  • ->> returns an array element or object field as text.
  • json_object_keys returns the set of keys in the JSON object, and only displays the "outer" object.

For example, suppose we have the following 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":""
}

Using the JSON functions and operators above, we could write the below SQL query to output the text of each message key:

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

Breaking it down, the functions and operators do the following:

  1. Find the notes key
  2. Extract the variable keys for each message, within the sub-JSON object notes
  3. Gets the message text for each variable message key
  4. De-nests the string of messages stored in a JSONB field

We can apply this logic to define dimensions based on parsed JSON using the Redshift functions and operators in a sql parameter:

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

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

The appropriate values from the JSON blob will be extracted as dimension values.

Related articles