Get array from json in Bigquery

Question

I'm trying to get the data from a JSON in BigQuery. This JSON is Stored in a one-column table. So far, I've been able to get only the "variables" array, with the following:

Select  JSON_QUERY_ARRAY(Column1, '$.sessions[0].variables') FROM Table

How can I get the other values/arrays (sessionMessage and events)? I can't make it work.. I've tried with:

  JSON_VALUE(Column1, '$.sessions[0].conversation')
  JSON_QUERY_ARRAY(Column1, '$.sessions[0].sessionMessages')

But I get only empty values (The original json has values inside this arrays..)

{
   "fromDate":"2020-04-10T23:47:17.161Z",
   "pageRows":151,
   "sessions":[
      {
         "variables":[],
         "sessionDate":"2020-04-10T23:47:17.161Z",
         "botMessages":2,
         "userHasTalked":"true",
         "topics":[
            "TOPIC1"
         ],
         "sessionId":"WXXXSXSXSXXXQ_2020-01-00T23:47:17.161Z",
         "platformContactId":"XXXXXXX-XXXXXXX-XXXXXXXXXXXXXX",
         "sessionMessages":[.....],
         "queues":[
            "QUEUE1",
            "QUEUE2"
         ],
         "customerId":"SSDSDS",
         "userMessages":2,
         "operatorMessages":1,
         "sessionMessagesQty":2,
         "sessionStartingCause":"Organic",
         "channelId":"IDCHANEL",
         "conversation":"https://url.com",
         "events":[.....]
      }
   ],
   "toDate":"2020-04-10T23:47:17.161Z",
   "hasMore":true,
   "pageToken":"XXXXXXXXXXXXXX"
}

Yun Zhang · Accepted Answer · 2021-04-21 06:03:00Z

There is nothing wrong with the function and JSONPath that you used, but your sample JSON file has some unexpected thing, like [.....], removing/replacing those and query below works fine:

WITH a as (select 
"""
{
   "fromDate":"2020-04-10T23:47:17.161Z",
   "pageRows":151,
   "sessions":[
      {
         "variables":[],
         "sessionDate":"2020-04-10T23:47:17.161Z",
         "botMessages":2,
         "userHasTalked":"true",
         "topics":[
            "TOPIC1"
         ],
         "sessionId":"WXXXSXSXSXXXQ_2020-01-00T23:47:17.161Z",
         "platformContactId":"XXXXXXX-XXXXXXX-XXXXXXXXXXXXXX",
         "sessionMessages":[1,2,3],
         "queues":[
            "QUEUE1",
            "QUEUE2"
         ],
         "customerId":"SSDSDS",
         "userMessages":2,
         "operatorMessages":1,
         "sessionMessagesQty":2,
         "sessionStartingCause":"Organic",
         "channelId":"IDCHANEL",
         "conversation":"https://url.com",
         "events":[],
      }
   ],
   "toDate":"2020-04-10T23:47:17.161Z",
   "hasMore":true,
   "pageToken":"XXXXXXXXXXXXXX"
}
""" data)
SELECT JSON_VALUE(data, '$.sessions[0].conversation'),
       JSON_QUERY_ARRAY(data, '$.sessions[0].sessionMessages')
FROM a;

Collectives™ on Stack Overflow

Get array from json in Bigquery

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related