Hi, I need help with accessing json fields in my t...
# troubleshooting
v
Hi, I need help with accessing json fields in my table. I've added json indexes to these fields but I'm unable to use json extract, json match for this. i also have column which is array of objects. We got these events from big query , want to store in pinot and query here. with json match, im getting no records found even though it is present.
Copy code
SELECT
  user_id,
  event_name,
  geo
FROM events_intraday_20250719
WHERE JSON_MATCH(
  geo,
  '"$.country"=''India'''
)
LIMIT 10;
got no records found. geo column :
Copy code
"{'city': 'Delhi', 'country': 'India', 'continent': 'Asia', 'region': 'Delhi', 'sub_continent': 'Southern Asia', 'metro': '(not set)'}"
Copy code
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 :
Copy code
"{'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
Copy code
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
Copy code
{
  "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.
b
Hi. I believe json parser expects strings within double quotes only and is likely failing on each row, leaving index empty.
🌟 1
m
@Bolek Ziobrowski is there a way to confirm your suspicion?
@Venkat Sai Ram JSON should be double-quoted right? Can you fix that and try?
🌟 1
b
I took the json Venkat shared and ran it through flattening method and got
JsonParseException: Unexpected character (''' (code 39)): was expecting double-quote to start field name
.
🌟 1
v
yes, that is the issue. its fixed now. can you also help me with a query. I want to access certain fields, problem is the column is strcutured as array of objects. event_params column
Copy code
[{"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
Copy code
SELECT
  REGEXP_EXTRACT(event_params, '"key":"ga_session_id".*?"int_value":([0-9]+)', 1) AS session_id
FROM 
  events_intraday_20250614
LIMIT 20
Copy code
SELECT 
  event_params
FROM 
  events_intraday_20250614
WHERE 
  REGEXP_EXTRACT(event_params, '"key":"ga_session_id".*?"int_value":([0-9]+)', 1) = ''
LIMIT 10