Convert XPath and JSONPath Expressions to Snowflake Queries

XPath versus JSONPath

Remember the days when XML was the biggest hype in high-tech? Before being actually replaced by JSON, people came up with XSLT, XQuery and (my favourite!) XPath. The last was a parser allowing you to specify one single expression and extract all sorts of nodes and attributes from a complex XML document.

Someone came up later with a similar XPath specification for JSON, called JSONPath. And I love it, because it uses the fluent functional approach with the dot notation most object-oriented programmers are already familiar with.

Here is how most of these XPath and JSONPath expressions will translate into valid equivalent Snowflake queries on semi-structured JSON data.

Preparing our JSON Data

The semi-structured JSON data we will use in all these samples contain a “store” with four “books” — each written by a different “author” — and one red “bicycle”. To keep it simple, let’s save it into a single-cell single-column table in Snowflake:

create database json_path;

create table json_table(v variant) as
  select '{ "store": {
    "book": [ 
      { "category": "reference",
        "author": "Nigel Rees",
        "title": "Sayings of the Century",
        "price": 8.95
      },
      { "category": "fiction",
        "author"…