0

I have already posted this question, but now Im trying to achieve this with BigQuery.

I have JSON data like below. 

{
  "userid": null,
  "appnumber": "9",
  "trailid": "1547383536",
  "visit": [{
      "visitNumber": "1",
      "time": "0",
      "hour": "18",
      "minute": "15"
    },
    {
      "visitNumber": "2",
      "time": "2942",
      "hour": "18",
      "minute": "15"
    }
  ]
}

I want to extract the visit array values dynamically.

Like below: (pipe demited column)

userid,appnumber| trailid | 
visit.visitnumber | visit.time | visit.hour | visit.minute | 
visit.visitnumber | visit.time | visit.hour | visit.minute

If you see I have 2 json elements inside the visit array. So I want to extract visitNumber, time, hour, minute dynamically. Sometime I may have 3 or 5 values inside the array, so It should extract all 3 or 5 json automatically(I mean dynamically).

There is a common way to extract this like JsonExtractScalar(JsonExtract(visit,'$.[0].visitnumber') (This syntax may be wrong, but similar syntax we'll use. So here Im manually using [0] to extract fist element in the array.

If it has 10+ elements then I should use [0]...[1]...[2]....[10]. This thing I want to solve, somehow without mentioning all elements it should dynamically pick all 10 elements and extract it.

Could someone help me with the extract queries?

TheDataGuy
  • 2,712
  • 6
  • 37
  • 89
  • are you expecting number of columns in output depend on number of elements in array multiply by number of elements within each array's elements?! please clarify expected output! – Mikhail Berlyant Jan 29 '19 at 18:24
  • The output should be the number of elements should become a column. If I have 2 elements inside the array then I need to get 2 columns. If I have 10 elements inside the array then I need to get 10 columns. – TheDataGuy Jan 29 '19 at 19:31
  • and what if in one row you have 10 elements and in another row you have 20 elements? – Mikhail Berlyant Jan 29 '19 at 19:32
  • I taught to put this on my previous comment , so 1st row contains 10elements, 2nd row contains 20 elements. 10 elements are common between them. So I should get 20 columns but for first row, it can return null for other 10columns. But I saw the data set, it has static values. Not sure in future. – TheDataGuy Jan 29 '19 at 19:36
  • so, you are looking for pivot'ing your data - search SO more - there are plenty of posts on that subject – Mikhail Berlyant Jan 29 '19 at 19:38
  • Sure Mikhail, but I'm hearing this term first time, would you mind share one example link, then I'll understand and check further. – TheDataGuy Jan 29 '19 at 19:40
  • 1
    sure, just few examples for you to get an idea: [How to pivot a table in BigQuery using standard SQL?](https://stackoverflow.com/q/54353970/5221944) ; [How do you pivot data in bigquery standard SQL without manual hardcoding?](https://stackoverflow.com/q/51698234/5221944) – Mikhail Berlyant Jan 29 '19 at 19:44
  • Thanks Mikhail, I'll check this out. – TheDataGuy Jan 29 '19 at 19:46

0 Answers0