0

i need to get count of item has been sold so iam exporting my firestore data to big query i have collection in firestore and i have array of object enter image description here

i need to export forestore data to big query

line_items
[
  {
    price:10,
    qyt:2,
   name:car
   },
 { 
   price:4,
   qyt:1,
   name:pen
  },
 ]

i have created schema.json and added the type as array and exported the collection data


  {
    "name": "line_items",
    "type": "array"
  }

now iam trying to write query in cloud.google to get this result in


name |qty
car  | 2
pen  | 1

my issue im not able to convert my array of object to columns so i can get the counts of item qty has been sold

enter image description here

this is how the data looks in big query it is not showing as array of object

enter image description here

Louna Akkad
  • 55
  • 8
  • It might that you haven't unnested your data. but It kind of looks like your schema is wrong. Can you share the errors you are getting and shot of the schema as it appear in bigquery? – Cleanbeans Nov 26 '21 at 10:04
  • this is my schema i have defined line_items as array. https://pasteboard.co/0jZLFeL8aqzU.png – Louna Akkad Nov 26 '21 at 19:01
  • the error is Cannot access field barcode on a value with type ARRAY at [2:19] i think im getting array of string that is why i can not unset check:https://www.photobox.co.uk/my/photo/full?photo_id=504281597889 – Louna Akkad Nov 26 '21 at 19:07
  • Unfortunately I can see that image. You've probably read this https://cloud.google.com/bigquery/docs/loading-data-cloud-firestore#cloud_firestore_options might be worth a quick read if not. You shouldn't have to create the schema if importing from firestore. A `map` in firestore is a `record` type in bigquery, so your schema isn't right. – Cleanbeans Nov 27 '21 at 23:10

1 Answers1

0

You have to UNNEST an array before getting elements

SELECT name, qty FROM your_dataset.your_table, UNNEST(line_items)
Timogavk
  • 809
  • 1
  • 7
  • 20