Using the map schema type

A map schema is an array of entries containing a key and a value.

Creating a map

You can create a map from existing data using a SELECT ENTRY clause. This allows you to define an identifier or expression to use as a key, and an identifier, an expression or a sub-query to use as a value. The clause should be formatted as follows:

SELECT ENTRY {
  key = expression,
  value = expression or sub-query
  }

You can implicitly assign an identifier as the key or value of the map with the following syntax:

SELECT ENTRY {
  key identifier, value identifier
}

In this case, the value of the first identifier is used as the key and the value of the second identifier is used as the value. If at least one of the map elements is assigned explicitly, the order is not important.

For example, with the following input data:

{
   "order":[
      {
         "orderno":1001,
         "custid":"C41",
         "order_date":"2017-04-29",
         "ship_date":"2017-05-03",
         "items":[
            {
               "itemno":347,
               "qty":5,
               "price":19.99
            },
            {
               "itemno":193,
               "qty":2,
               "price":28.89
            }
         ]
      },
      {
         "orderno":1002,
         "custid":"C13",
         "order_date":"2017-05-01",
         "ship_date":"2017-05-03",
         "items":[
            {
               "itemno":460,
               "qty":95,
               "price":100.99
            },
            {
               "itemno":680,
               "qty":150,
               "price":8.75
            }
         ]
      }
   ]
}

You can use the following query to create a map where the key is the orderno element and the value is an array of itemno elements:

FROM order
SELECT ENTRY {
  value = (
    FROM items
    SELECT itemno
  ),
  orderno
}

In this case, the following result is returned:

You can also convert a record to a map using the toMap function. For example, with the input value used above, you can use the following query to convert the order record to a map, which allows you to call map elements using their key with the syntax map_identifier['key'].

FROM order AS o
LET $o = toMap(o)
SELECT {
  id = concatWith('-',$o['custid'], $o['orderno']),
  total = (
    FROM items
    SELECT sum(price)
  )
}

The following result is returned:

[
   {
      "id":"C41-1001",
      "total":48.88
   },
   {
      "id":"C13-1002",
      "total":109.74
   }
]

For more information about the toMap function, see Special functions.

Reading a map

When your data contains a map, you can select their keys and values in your query. For example, with an input named properties containing the following data:

{
   "author":"Margaret Atwood",
   "title":"The Handmaid's Tale",
   "isbn":"978-0099740919"
}

You can read the JSON content as a map using the following Avro schema:

{
   "type":"map",
   "name":"properties",
   "values":"string"
}

You can then use this query to return an array of records with an element containing the key, and another containing the value:

FROM properties.entry
SELECT {key, value}

This returns the following result:

[
   {
      "key":"author",
      "value":"Margaret Atwood"
   },
   {
      "key":"title",
      "value":"The Handmaid's Tale"
   },
   {
      "key":"isbn",
      "value":"978-0099740919"
   }
]

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – please let us know!

Leave your feedback here