Working with Arrays in Query String Array JSON

Introduction

As data ingestion grows, dealing with array-like data structures becomes increasingly common. Query string array JSON is a format used by various databases and technologies to store arrays of JSON objects. In this article, we’ll explore how to work with query string array JSON, focusing on extracting specific fields from the array.

Background

Query string array JSON typically involves storing an array of JSON objects in a single column within a table. Each element in the array represents a separate JSON object, which may contain various fields such as strings, numbers, dates, or even binary data. When working with this format, it’s essential to understand how to manipulate and extract specific fields from the array.

The Challenge of Querying Arrays

One of the primary challenges when dealing with query string array JSON is that many databases do not fully support standard JSONPath expressions. This means that conventional methods for extracting specific fields from an array may not work as expected. In this section, we’ll discuss how to overcome this challenge by using alternative approaches.

Casting JSON to an Array of JSON

One way to overcome the limitations of querying arrays is to cast the JSON string to an array of JSON objects. This can be achieved using a combination of SQL functions and data transformations.

Let’s consider the following sample data:

-- Sample data
WITH dataset (client, information) AS (
    VALUES (1, '[ { "ProductId" : { "$binary" : "7KgRQKabqkuxE+1pSw9b7Q==", "$type" : "03" }, "Risk" : "0", "Due_data" : { "$date" : 1634860800000 }} ]')
)

To extract fields from the array, we can use the following SQL query:

-- Query
SELECT client,
    r.product_id,
    r.risk,
    r.ts
FROM dataset
    CROSS JOIN UNNEST(
        transform(
            cast(json_parse(information) as array(json)), -- parse json and treat as array of json
            json->cast( -- transform concrete json to row
                ROW(
                    json_extract_scalar(json, '$.ProductId["$binary"]'),
                    json_extract_scalar(json, '$.Risk'),
                    from_unixtime(
                        cast(
                            json_extract_scalar(json, '$.Due_data["$date"]') AS BIGINT
                        ) / 1000 -- transform timestamp to date
                    )
                ) as ROW(product_id VARCHAR, risk INT, ts TIMESTAMP)
            )
        )
    ) as t(r)

Output

The output of this query will be a table with the extracted fields:

Handling Array Transformations

While the previous query extracts individual fields from the array, we may need to perform more complex transformations on the data.

For instance, let’s say we want to extract a specific product ID that involves additional processing:

-- Query with transformation
SELECT client,
    r.product_id,
    r.risk,
    r.ts,
    case
        when r.product_id = '7KgRQKabqkuxE+1pSw9b7Q==' then 'match'
        else null
    end as match_product_id
FROM dataset
    CROSS JOIN UNNEST(
        transform(
            cast(json_parse(information) as array(json)), -- parse json and treat as array of json
            json->cast( -- transform concrete json to row
                ROW(
                    r.product_id,
                    r.risk,
                    from_unixtime(
                        cast(
                            json_extract_scalar(json, '$.Due_data["$date"]') AS BIGINT
                        ) / 1000 -- transform timestamp to date
                    )
                ) as ROW(product_id VARCHAR, risk INT, ts TIMESTAMP)
            )
        )
    ) as t(r)

In this example, we’ve added a match_product_id column that checks if the extracted product ID matches the expected value. This demonstrates how more complex transformations can be applied to the data.

Conclusion

Working with query string array JSON requires careful consideration of how to extract specific fields from the array. By casting the JSON string to an array of JSON objects and using SQL functions like UNNEST and json->cast, we can overcome the limitations of querying arrays.

Last modified on 2024-03-31