Dot Notation For Arrays

Dot notation is used to navigate through nested JSON keys to retrieve values. A single dot denotes moving from one key to the next.

For example: house.floor.room.window refers to:

  • The window key in the…
  • room key in the…
  • floor key in the…
  • house

That’s pretty straightforward except when navigating through JSON arrays like this data.

'{"Name" : "Marty",
 "Reason" : [ { "Medium" : "Movie",
                "Title" : "Madagascar",
                "Year" : 2005 },
              { "Medium" : "Movie",
                "Title" : "Madagascar Escape 2 Africa",
                "Year" : 2008 },
              { "Medium" : "Movie",
           "Title" : "Madagascar 3 : Europes Most Wanted",
              "Year" : 2012 } ] }');

'{"Name" : "Zigby",
 "Reason" : [ { "Medium" : "Book",
                "Title" : "Zigby Camps Out",
                "Year" : 2002 },
              { "Medium" : "Book",
                "Title" : "Zigby Hunts For Treasure",
                "Year" : 2002 },
              { "Medium" : "Book",
                "Title" : "Zigby and the Ant Invaders",
                "Year" : 2003 } ] }');

How do we get to the second title of each row? How do we get to all rows having a title with a specific set of characters? Array pointers in square brackets to the rescue in 12.2! Here’s two examples:

  • Reason[0].Title returns ‘Madagascar’ and ‘Zigby Camps Out’
  • Reason[*].Title returns all of the Titles as JSON

Some important point are:

  • Array counters start at zero
  • An array pointer of * returns all entries
  • If multiple values are found they will be returned as JSON (even if they are scalars)
  • Single values will be returned as scalars

For more examples see this post on livesql.

Thanks for reading!