Venkat Sai Ram
07/30/2025, 9:00 AMSELECT
user_id,
event_name,
geo
FROM events_intraday_20250719
WHERE JSON_MATCH(
geo,
'"$.country"=''India'''
)
LIMIT 10;
got no records found.
geo column :
"{'city': 'Delhi', 'country': 'India', 'continent': 'Asia', 'region': 'Delhi', 'sub_continent': 'Southern Asia', 'metro': '(not set)'}"
SELECT
json_extract_scalar(app_info, '$.id', 'STRING', 'null') AS app_id,
json_extract_scalar(app_info, '$.version', 'STRING', 'null') AS app_version
FROM events_intraday_20250719
LIMIT 10;
all null's is the result i got.
app_info column :
"{'id': 'com.aadhan.hixic', 'version': '5.7.6', 'install_store': None, 'firebase_app_id': '1:700940617518:android:4c5cd93d642b6868', 'install_source': 'manual_install'}"
if I remove the default null, i got
Error Code: 200 (QueryExecutionError)
Caught exception while doing operator: class org.apache.pinot.core.operator.query.SelectionOnlyOperator on segment events_intraday_20250719_OFFLINE_1752863401870004_1752949768919000_12: Cannot resolve JSON path on some records. Consider setting a default value.
Table Config
{
"OFFLINE": {
"tableName": "events_intraday_20250719_OFFLINE",
"tableType": "OFFLINE",
"segmentsConfig": {
"replication": "1",
"timeColumnName": "event_timestamp",
"minimizeDataMovement": false
},
"tenants": {
"broker": "DefaultTenant",
"server": "DefaultTenant"
},
"tableIndexConfig": {
"aggregateMetrics": false,
"invertedIndexColumns": [
"event_name",
"platform",
"session_traffic_source_last_click",
"traffic_source"
],
"nullHandlingEnabled": false,
"enableDefaultStarTree": false,
"enableDynamicStarTreeCreation": false,
"columnMajorSegmentBuilderEnabled": true,
"skipSegmentPreprocess": false,
"optimizeDictionary": false,
"optimizeDictionaryForMetrics": false,
"optimizeDictionaryType": false,
"noDictionarySizeRatioThreshold": 0.85,
"rangeIndexVersion": 2,
"jsonIndexColumns": [
"app_info",
"device",
"event_params",
"geo",
"privacy_info",
"user_properties"
],
"autoGeneratedInvertedIndex": false,
"createInvertedIndexDuringSegmentGeneration": false,
"loadMode": "MMAP"
},
"metadata": {},
"ingestionConfig": {
"batchIngestionConfig": {
"segmentIngestionType": "APPEND",
"segmentIngestionFrequency": "DAILY",
"consistentDataPush": false
},
"continueOnError": false,
"retryOnSegmentBuildPrecheckFailure": false,
"rowTimeValueCheck": false,
"segmentTimeValueCheck": true
},
"isDimTable": false
}
}
can you help me with this. im happy to provide addtional information.Bolek Ziobrowski
07/30/2025, 2:32 PMMayank
Mayank
Bolek Ziobrowski
07/31/2025, 7:32 AMJsonParseException: Unexpected character (''' (code 39)): was expecting double-quote to start field name .Venkat Sai Ram
07/31/2025, 11:03 AM[{"key":"firebase_screen_class","value":{"string_value":"MainFeedActivity","int_value":null,"float_value":null,"double_value":null}},{"key":"ContentID","value":{"string_value":"684d52fa5e106f82eced8b87","int_value":null,"float_value":null,"double_value":null}},{"key":"AadhanID","value":{"string_value":"93464c9fe6fc58e9","int_value":null,"float_value":null,"double_value":null}},{"key":"ContentTitle","value":{"string_value":"ప్రమాదంలో చనిపోయిన ఎయిర్ హోస్టెస్ ఫొటో చూడండి","int_value":null,"float_value":null,"double_value":null}},{"key":"ContentType","value":{"string_value":"ShortNews","int_value":null,"float_value":null,"double_value":null}},{"key":"PublishedDate","value":{"string_value":"1749898499","int_value":null,"float_value":null,"double_value":null}},{"key":"ga_session_number","value":{"string_value":null,"int_value":21,"float_value":null,"double_value":null}},{"key":"From","value":{"string_value":"main_feed","int_value":null,"float_value":null,"double_value":null}},{"key":"firebase_conversion","value":{"string_value":null,"int_value":1,"float_value":null,"double_value":null}},{"key":"ContentBy","value":{"string_value":"Ravikumar P","int_value":null,"float_value":null,"double_value":null}},{"key":"firebase_screen_id","value":{"string_value":null,"int_value":198944244771298644,"float_value":null,"double_value":null}},{"key":"ContentCategory","value":{"string_value":"జాతీయం","int_value":null,"float_value":null,"double_value":null}},{"key":"ViewDuration","value":{"string_value":null,"int_value":4224,"float_value":null,"double_value":null}},{"key":"engaged_session_event","value":{"string_value":null,"int_value":1,"float_value":null,"double_value":null}},{"key":"Sponsored","value":{"string_value":null,"int_value":0,"float_value":null,"double_value":null}},{"key":"firebase_event_origin","value":{"string_value":"app","int_value":null,"float_value":null,"double_value":null}},{"key":"ga_session_id","value":{"string_value":null,"int_value":1749899954,"float_value":null,"double_value":null}},{"key":"AdvertisingID","value":{"string_value":"c2b67521-6e62-4471-a719-ce7f778be633","int_value":null,"float_value":null,"double_value":null}},{"key":"ContentLanguage","value":{"string_value":"Telugu","int_value":null,"float_value":null,"double_value":null}},{"key":"UserID","value":{"string_value":"93464c9fe6fc58e9","int_value":null,"float_value":null,"double_value":null}}]
i wrote query using regex but it seemed hacky way to do this, is there a better solution to this.
the data we are getting is from big query so can't change the structure.
this is the query, i wrote
SELECT
REGEXP_EXTRACT(event_params, '"key":"ga_session_id".*?"int_value":([0-9]+)', 1) AS session_id
FROM
events_intraday_20250614
LIMIT 20
SELECT
event_params
FROM
events_intraday_20250614
WHERE
REGEXP_EXTRACT(event_params, '"key":"ga_session_id".*?"int_value":([0-9]+)', 1) = ''
LIMIT 10Mayank