Complex Data: Intermediate

Search, sort, and modify array contents

CARDINALITY(array) returns the number of elements in an array. This is essential for understanding data distribution and filtering based on collection size.

Use CARDINALITY to identify orders with many items, filter out empty arrays, or calculate averages across variable-length collections.

CONTAINS(array, element) returns true if the specified element exists in the array. This enables filtering rows based on array contents without expanding the array.

The CONTAINS function performs an efficient membership test. For large arrays or frequent lookups, consider whether the data model should use a separate table with proper indexing instead.

ELEMENT_AT(array, index) returns the element at the specified position. Unlike bracket notation, ELEMENT_AT handles out-of-bounds indices gracefully by returning NULL.

Negative indices count from the end: ELEMENT_AT(array, -1) returns the last element. This is useful when you need the most recent item in a chronologically ordered array.

SELECT
  event_id,
  ___(___) AS tag_count
FROM event_data

With CARDINALITY you can quickly measure any array. This pairs naturally with WHERE clauses to find rows where array size meets specific criteria.

Remember: CARDINALITY returns 0 for empty arrays and NULL for NULL arrays, so handle both cases in production queries.

Remove duplicates and nulls from arrays

ARRAY_DISTINCT(array) removes duplicate elements, returning an array with only unique values. Order is not guaranteed in the result.

Combining ARRAY_DISTINCT with CARDINALITY gives you the count of unique values, useful for metrics like unique page views per session or distinct products viewed.

ARRAY_SORT(array) returns the array with elements in ascending order. For strings, this is lexicographic order. For numbers, it is numeric order.

ARRAY_UNION merges two arrays and removes duplicates. ARRAY_INTERSECT returns only elements present in both arrays. These are the array equivalents of SQL UNION and INTERSECT operations.

SELECT
  event_id,
  ___(___, ___) AS second_tag
FROM event_data

ELEMENT_AT provides safe positional access. Unlike bracket notation in some systems, out-of-bounds indices return NULL rather than errors.

Combine ELEMENT_AT with COALESCE to provide default values when an index might not exist.

Merge, filter, and transform map data

MAP_KEYS(map) returns an array containing all keys in the map. This is essential for discovering what properties exist in a flexible schema.

MAP_VALUES(map) returns an array of all values in the map, in the same order as their corresponding keys from MAP_KEYS.

Combining MAP_KEYS and MAP_VALUES with array functions like CONTAINS lets you search maps for specific keys or values without knowing the complete schema.

MAP_ENTRIES(map) returns an array of key-value pair structures, enabling iteration over the entire map. Each entry contains a key and value field.

SELECT
  event_id
FROM event_data
WHERE ___(___, ___)

The CONTAINS function works on both arrays and the arrays returned by MAP_KEYS, making it versatile for membership checks across data structures.

Pair CONTAINS with MAP_KEYS to check for property existence, or with raw arrays for value membership tests.

Next you will see how JSON_EXTRACT_SCALAR simplifies filtering and joining on values embedded inside JSON documents.

Get clean scalar values from JSON fields

While JSON_EXTRACT returns JSON-typed values, JSON_EXTRACT_SCALAR returns plain SQL strings. This makes it perfect for filtering, joining, and comparing JSON values without type conversion.

Recall that JSON_EXTRACT preserves JSON formatting, including quotes around strings:

This example shows how to extract scalar values from JSON and filter rows based on those values.

The WHERE clause uses a simple string comparison without escaped quotes. This is much cleaner than working with JSON_EXTRACT.

JSON_EXTRACT_SCALAR enables efficient filtering on JSON fields. Some query engines can even push these filters down to storage for better performance.

JSON_EXTRACT_SCALAR only works with scalar JSON values: strings, numbers, and booleans. Attempting to extract an array or object returns NULL.

Numbers are returned as strings. If you need numeric operations, cast the result: CAST(json_extract_scalar(...) AS DOUBLE).

SELECT
  event_id,
  ___(___) AS prop_keys
FROM event_data

MAP_KEYS reveals which properties each row contains, making it essential for auditing schema consistency across flexible data.

Use MAP_KEYS with CARDINALITY to count properties, or with CONTAINS to check for specific keys.

The final section covers CAST, which bridges JSON values to native SQL types so you can use all the functions learned in this lesson.

Convert raw JSON into queryable types

Converting JSON to native array or map types unlocks the full power of array and map functions. Use CAST to transform extracted JSON into structured SQL types.

Converting JSON arrays to native SQL arrays unlocks the full set of array functions like CARDINALITY and CONTAINS.

Use CAST with ARRAY(type) to convert a JSON array into a native SQL array.

After casting, you can use CARDINALITY, CONTAINS, ELEMENT_AT, and all other array functions on the result.

Converting JSON objects to native SQL maps enables efficient key-based lookups and map functions.

Use CAST with MAP(key_type, value_type) to convert a JSON object into a native SQL map.

The target type MAP(VARCHAR, VARCHAR) specifies both key and value types. All keys must convert to the key type, and all values must convert to the value type.

This reference shows common JSON structures and their corresponding SQL type specifications.

SELECT
  event_id,
  ___(
    ___(
      JSON_EXTRACT(payload, '$.tags')
      AS ___
      )
    ) AS tag_count
FROM event_data

Casting JSON to native types is the bridge between semi-structured and structured data. Once cast, all array and map functions become available.

The WITH clause pattern of casting once and referencing many times is the recommended approach for production queries involving JSON conversion.

You now have a complete toolkit for working with arrays, maps, and JSON in SQL. These functions are fundamental to processing semi-structured data at scale.