Parse fields containing JSON data

Version 2

    Description:

     

    This demonstrates one way to extract data from a field that contains JSON data (e.g. key/value pairs).

    The approach using a regex to pattern match on the key and then extract the value.

     

    Example Calculation:

     

    // Extract the value of a key/value pair from a JSON string

     

    // In this example we want the value of a key called highwaytype, eg:

    // {"highwaytype":"motorway"}

     

    // The regex is via http://stackoverflow.com/questions/14349889/how-to-use-a-regular-expression-to-extract-json-fields

    // and tested with http://regexr.com

     

    REGEXP_EXTRACT([JSON Column],'"highwaytype":"((\\"|[^"])*)"')

     

    // Here is a variation to get the value of a float, removing a trailing comma after the value using !,:

    // float(

     

    REGEXP_EXTRACT([Weather],'"temp":((\\"|[^"!,])*)')

     

     

     

    Inputs and Setup: (for table calculations, delete if not applicable)

     

     

     

     

    Partitioning and Addressing: (for table calculations, delete if not applicable)

     

     

     

     


    Comments:

     

     

     

     

    Related Functions:

     

     

     

    Further Reading/Examples: