Query fields with mixed types
Inspect JSON values and convert or extract their stored types.
Query fields with mixed types
JSON paths can hold different types in different rows. Inspect each type before you choose a conversion.
Before you begin
- Use the
analyticsdatabase. Create it with the database API if it does not exist. - Set
RAWTREE_DATABASEtoanalyticsfor the API and CLI examples. - Use an
eventstable with avaluepath and atagspath.
1. Inspect the stored types
List each value and its concrete type.
SELECT value, dynamicType(value) AS value_type FROM events ORDER BY value_type2. Convert compatible values
Convert values to Int64 when that conversion matches your data.
SELECT value::Int64 AS value_number FROM eventsUse accurateCastOrNull when invalid values must return NULL.
SELECT accurateCastOrNull(value, 'Int64') AS value_number FROM events3. Extract one exact type
Use .:Int64 to select only values stored as Int64.
SELECT __raw_data.value.:Int64 AS exact_integer FROM events4. Inspect arrays
Check the type before you apply array functions.
SELECT dynamicType(tags) AS tags_type, tags FROM eventsWhen an indexed or sorting-key path uses a cast, check the query plan for index
use. accurateCastOrNull and exact-type extraction can prevent that use.
Check the result
The value_type result shows Int64 and String for the sample values.
The tags_type result shows the array type for array values.
Inspect hints even when a query succeeds. These runtime type-compatibility
warnings can identify incompatible values that an operation skips or replaces
with NULL or a default result.
# Inspect the hints field in the JSON result.
# With values 42, "42", and null, the total is 42.
# The warning identifies the String values excluded by sum.
curl -X POST \
"https://api.rawtree.com/v1/query?database=$RAWTREE_DATABASE" \
--header "Authorization: Bearer $RAWTREE_API_KEY" \
--header "Content-Type: application/json" \
-d @- <<'JSON'
{"sql": "SELECT sum(value) AS total FROM events"}
JSON# Inspect the hints field in the JSON result.
# With values 42, "42", and null, the total is 42.
# The warning identifies the String values excluded by sum.
rtree query --database "$RAWTREE_DATABASE" --json \
"SELECT sum(value) AS total
FROM events"-- Inspect the hints field in the JSON result.
-- With values 42, "42", and null, the total is 42.
-- The warning identifies the String values excluded by sum.
SELECT sum(value) AS total
FROM analytics.events
FORMAT JSON;Use dynamicType to inspect the types named in the warning. Choose a conversion
or exact extraction that matches the field's meaning. Count values that the
chosen conversion excludes.
# Convert numeric strings when they represent numbers.
# Count non-null values that fail conversion.
# With values 42, "42", and null, total is 84 and invalid_values is 0.
curl -X POST \
"https://api.rawtree.com/v1/query?database=$RAWTREE_DATABASE" \
--header "Authorization: Bearer $RAWTREE_API_KEY" \
--header "Content-Type: application/json" \
-d @- <<'JSON'
{"sql": "WITH accurateCastOrNull(value, 'Int64') AS number SELECT sum(number) AS total, countIf(value IS NOT NULL AND number IS NULL) AS invalid_values FROM events"}
JSON# Convert numeric strings when they represent numbers.
# Count non-null values that fail conversion.
# With values 42, "42", and null, total is 84 and invalid_values is 0.
rtree query --database "$RAWTREE_DATABASE" --json \
"WITH accurateCastOrNull(value, 'Int64') AS number
SELECT
sum(number) AS total,
countIf(value IS NOT NULL AND number IS NULL) AS invalid_values
FROM events"-- Convert numeric strings when they represent numbers.
-- Count non-null values that fail conversion.
-- With values 42, "42", and null, total is 84 and invalid_values is 0.
WITH accurateCastOrNull(value, 'Int64') AS number
SELECT
sum(number) AS total,
countIf(value IS NOT NULL AND number IS NULL) AS invalid_values
FROM analytics.events
FORMAT JSON;Some operations can skip incompatible types without a hint. Use explicit counts to check which values contribute to the result. See Runtime type-compatibility warnings for comparison defaults and other limits.