CODEWITHSUNDEEP
sqlgoogle-bigquery
Greg Fenton
Greg Fenton

Reputation: 2808

Ability to get the "index" (or ordinal value) for each array entry in BigQuery?

In a data column in BigQuery, I have a JSON object with the structure:

{
 "sections": [
   {
     "secName": "Flintstones",
     "fields": [
       { "fldName": "Fred", "age": 55 },
       { "fldName": "Barney", "age": 44 }
     ]
   },
   {
     "secName": "Jetsons",
     "fields": [
       { "fldName": "George", "age": 33 },
       { "fldName": "Elroy", "age": 22 }
     ]
   }
 ]}

I'm hoping to unnest() and json_extract() to get results that resemble:

id  | section_num  | section_name | field_num | field_name | field_age
----+--------------+--------------+-----------+------------+-----------
1   | 1            | Flintstones  | 1         | Fred       |  55
1   | 1            | Flintstones  | 2         | Barney     |  44
1   | 2            | Jetsons      | 1         | George     |  33
1   | 2            | Jetsons      | 2         | Elroy      |  22

So far, I have the query:

   SELECT id,
          json_extract_scalar(curSection, '$.secName') as section_name,
          json_extract_scalar(curField, '$.fldName') as field_name,
          json_extract_scalar(curField, '$.age') as field_age
     FROM `tick8s.test2` AS tbl
LEFT JOIN unnest(json_extract_array(tbl.data, '$.sections')) as curSection
LEFT JOIN unnest(json_extract_array(curSection, '$.fields')) as curField

that yields:

id  | section_name | field_name | field_age
----+--------------+------------+-----------
1   | Flintstones  | Fred       |  55
1   | Flintstones  | Barney     |  44
1   | Jetsons      | George     |  33
1   | Jetsons      | Elroy      |  22

QUESTION: I'm not sure how, if possible, to get the section_num and field_num ordinal positions from their array index values?

(If you are looking to duplicate my results, I have a table named test2 with 2 columns:

and I insert the data with:

insert into tick8s.test2 values (1, 
'{"sections": [' ||
       '{' ||
         '"secName": "Flintstones",' ||
         '"fields": [' ||
           '{ "fldName": "Fred", "age": 55 },' ||
           '{ "fldName": "Barney", "age": 44 }' ||
         ']' ||
       '},' ||
       '{' ||
         '"secName": "Jetsons",' ||
         '"fields": [' ||
           '{ "fldName": "George", "age": 33 },' ||
           '{ "fldName": "Elroy", "age": 22 }' ||
         ']' ||
       '}]}'
);

)

Upvotes: 2

Views: 1249

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269893

Do you just want with offset?

SELECT id,
       json_extract_scalar(curSection, '$.secName') as section_name,
       n_s,
       json_extract_scalar(curField, '$.fldName') as field_name,
       json_extract_scalar(curField, '$.age') as field_age,
       n_c
 FROM `tick8s.test2` tbl LEFT JOIN
       unnest(json_extract_array(tbl.data, '$.sections')
             ) curSection WITH OFFSET n_s LEFT JOIN
       unnest(json_extract_array(curSection, '$.fields')
             ) curField WITH OFFSET n_c;

Upvotes: 3

Related Questions