Refer to the exhibit. Which SQL snippet correctly extracts the 'total' value of the second order in the JSON object stored in a column named 'src'?
Exhibit
{
"customer": "Acme Corp",
"orders": [
{"id": "O1", "total": 100},
{"id": "O2", "total": 250}
]
}Trap 1: src:orders[2]:total
Snowflake arrays follow standard programming conventions and use zero-based indexing. Using [2] would attempt to access the third element in the 'orders' array, which does not exist in this exhibit, resulting in a NULL value rather than the desired second order's total.
Trap 2: src:orders.1.total
Dot notation is used for object keys, but it cannot be used to reference array indices directly in this manner. To access a specific element within an array, you must use square brackets, such as [1], to indicate the position of the element you wish to retrieve.
Trap 3: GET_PATH(src, 'orders/1/total')
The GET_PATH function uses dot notation (e.g., 'orders[1].total') or simple keys to navigate the path, not forward slashes like a file system directory. While GET_PATH is a valid function, the path string provided in this option uses an incorrect format for Snowflake path navigation.
- A
src:orders[2]:total
Why it fails: Snowflake arrays follow standard programming conventions and use zero-based indexing. Using [2] would attempt to access the third element in the 'orders' array, which does not exist in this exhibit, resulting in a NULL value rather than the desired second order's total.
- B
src:orders.1.total
Why it fails: Dot notation is used for object keys, but it cannot be used to reference array indices directly in this manner. To access a specific element within an array, you must use square brackets, such as [1], to indicate the position of the element you wish to retrieve.
- C
src:orders[1]:total
This is the correct syntax. 'src:orders' accesses the array, '[1]' selects the second element (since indexing starts at 0), and ':total' extracts the value associated with the 'total' key from that specific object within the array.
- D
GET_PATH(src, 'orders/1/total')
Why it fails: The GET_PATH function uses dot notation (e.g., 'orders[1].total') or simple keys to navigate the path, not forward slashes like a file system directory. While GET_PATH is a valid function, the path string provided in this option uses an incorrect format for Snowflake path navigation.