Extensions: Array fields in from GENERATE_SCHEMA_VIEWS remove the row from bigquery when empty or not present.

[iislucas]

[REQUIRED] Step 2: Describe your configuration

• Extension name: firestore-bigquery-export (Specifically the GENERATE_SCHEMA_VIEWS script)
• Extension version: 0.1.6

[REQUIRED] Step 3: Describe the problem

If you have array-typed objects and the array-typed object is empty, the rows are skipped in bigquery, where I think they should be NULL (or documentation should say the array must never be empty or your row wont be shown).

Steps to reproduce:

1. Create a schema with an array.
2. Make some value in the firestore not have the array value, or have it empty.
3. Search for the document in the "*_schema_latest" table generated by the script.

Expected result

You would find the row you searched for.

Actual result

You get an empty result.

[iislucas]

I think the issue is that array fields are being added with "CROSS JOIN", where they should be added with "LEFT JOIN" here: https://github.com/firebase/extensions/blob/master/firestore-bigquery-export/scripts/gen-schema-view/src/schema.ts#L260

[russellwheatley]

If I understand you correctly, I think this is by design as specified in the documentation:
https://github.com/firebase/extensions/blob/next/firestore-bigquery-export/guides/GENERATE_SCHEMA_VIEWS.md#cloud-firestore-arrays

The pertinent line:
If the array is empty, it will be ignored by the schema-views script.

[iislucas]

The 'it' in that sentence would normally be interpreted as "the array", rather that the whole row. I don't think "the row" is likely to be an interpretation anyone would expect/want.

But if that is really the current goal, then making the sentence clearer would fix it; and this can then become a feature request: don't ignore the row is an array is empty; ignore the array instead. I can't image any cases when ignoring the row would be good. That can be fixed/improved as per: https://github.com/firebase/extensions/issues/298#issuecomment-623715017 :)

[russellwheatley]

I agree with you @iislucas, it shouldn't ignore the row. I will take a look and get back to you.

[russellwheatley]

I had a look at implementing a LEFT JOIN at this spot for the changelog view:https://github.com/firebase/extensions/blob/master/firestore-bigquery-export/scripts/gen-schema-view/src/schema.ts#L260 & also at this spot for the latest view: https://github.com/firebase/extensions/blob/master/firestore-bigquery-export/scripts/gen-schema-view/src/snapshot.ts#L163. Whilst it solved the initial problem, It did generate a new issue. It generates rows for every document even if there is no schema related data in the document:

Maybe there is another way of creating a query that will maintain rows that have empty arrays whilst ignoring rows that have none of the schema properties. I'm not sure at this moment in time. It needs further investigation, or perhaps a decision on either LEFT JOIN or CROSS JOIN as desired behaviour.

[iislucas]

A more standard SQL-esk way might be to create a separate table for the array, a table that shares the document_name, and this would also enable you to easily support https://github.com/firebase/extensions/issues/293 ?

[jackdreilly]

Apologies if this is too off-topic: Would this issue go away if a nested representation for exporting were supported?

I've experimented w/ constructing a view of nested structs and array, and it seems quite generic and convenient (Example below).

Is there a reason that a flattened representation was preferred to a nested one?

SELECT (
  select array_agg(
    struct(
      timestamp_seconds(cast(json_extract_scalar(d, '$.date._seconds') as int64)) as date,
      json_extract_scalar(d, '$.text') as text,
      struct(
        json_extract_scalar(d, '$.user.name') as text,
        json_extract_scalar(d, '$.user.photo') as photo
      ) as user  
    )
) from unnest(json_extract_array(data, '$.comments')) as d) as comments,
json_extract_scalar(data, '$.reference') as reference,
struct(
  json_extract_scalar(data, '$.review.text') as text,
  json_extract_scalar(data, '$.review.reaction') as reaction,
  json_extract_scalar(data, '$.review.delivery_app') as delivery_app,
  json_extract_array(data, '$.review.emojis') as emojis,
  json_extract_array(data, '$.review.tags') as tags
) as review,
struct(
  json_extract_scalar(data, '$.restaurant.name') as text,
  json_extract_scalar(data, '$.restaurant.reference') as reference
) as restaurant,
json_extract_scalar(data, '$.dish') as dish,
json_extract_scalar(data, '$.meal_type') as meal_type,
timestamp_seconds(cast(json_extract_scalar(data, '$.date._seconds') as int64)) as date
FROM `table.firestore_export.discover_items_raw_latest` LIMIT 10;

[iislucas]

I've just been using the LEFT join thing as I found that I wanted to know if I had bad records, rather than hide them.

[karayu]

Hi Lucas,

Would it be possible for you to share some sample data and sample schema? This would really help us understand the use case.

Thanks!
Kara

[nwparker]

+1 to this issue

I see Lucas left things hanging, but I'm happy to answer any questions in regards to valid use cases @karayu

[nwparker]

Apologies if this is too off-topic: Would this issue go away if a nested representation for exporting were supported?

I've experimented w/ constructing a view of nested structs and array, and it seems quite generic and convenient (Example below).

Is there a reason that a flattened representation was preferred to a nested one?

SELECT (
  select array_agg(
    struct(
      timestamp_seconds(cast(json_extract_scalar(d, '$.date._seconds') as int64)) as date,
      json_extract_scalar(d, '$.text') as text,
      struct(
        json_extract_scalar(d, '$.user.name') as text,
        json_extract_scalar(d, '$.user.photo') as photo
      ) as user  
    )
) from unnest(json_extract_array(data, '$.comments')) as d) as comments,
json_extract_scalar(data, '$.reference') as reference,
struct(
  json_extract_scalar(data, '$.review.text') as text,
  json_extract_scalar(data, '$.review.reaction') as reaction,
  json_extract_scalar(data, '$.review.delivery_app') as delivery_app,
  json_extract_array(data, '$.review.emojis') as emojis,
  json_extract_array(data, '$.review.tags') as tags
) as review,
struct(
  json_extract_scalar(data, '$.restaurant.name') as text,
  json_extract_scalar(data, '$.restaurant.reference') as reference
) as restaurant,
json_extract_scalar(data, '$.dish') as dish,
json_extract_scalar(data, '$.meal_type') as meal_type,
timestamp_seconds(cast(json_extract_scalar(data, '$.date._seconds') as int64)) as date
FROM `table.firestore_export.discover_items_raw_latest` LIMIT 10;

Responding to this specific snippet. It doesn't quite work.

Instead you want something like this:

SELECT
    ARRAY(
        SELECT
            struct(
                timestamp_seconds(cast(json_extract_scalar(d, '$.date._seconds') as int64)) as date,
                json_extract_scalar(d, '$.text') as text,
                struct(
                    json_extract_scalar(d, '$.user.name') as text,
                    json_extract_scalar(d, '$.user.photo') as photo
                ) as user
            )
        FROM
            unnest(json_extract_array(data, '$.comments')) as d
    ) as comments,
    json_extract_scalar(data, '$.reference') as reference,
    struct(
        json_extract_scalar(data, '$.review.text') as text,
        json_extract_scalar(data, '$.review.reaction') as reaction,
        json_extract_scalar(data, '$.review.delivery_app') as delivery_app,
        json_extract_array(data, '$.review.emojis') as emojis,
        json_extract_array(data, '$.review.tags') as tags
    ) as review,
    struct(
        json_extract_scalar(data, '$.restaurant.name') as text,
        json_extract_scalar(data, '$.restaurant.reference') as reference
    ) as restaurant,
    json_extract_scalar(data, '$.dish') as dish,
    json_extract_scalar(data, '$.meal_type') as meal_type,
    timestamp_seconds(cast(json_extract_scalar(data, '$.date._seconds') as int64)) as date
FROM
    `table.firestore_export.discover_items_raw_latest`
LIMIT
    10;

Notice my use of array instead of array_agg

[pablomargareto]

I believe that nested representations would be the way to fix this. I need to do it as an intermediate step and store the results in a third table. Nested fields would be awesome!

[haynzz]

@russellwheatley I did not quite get when I would run into issues with the LEFT JOIN?

Whilst it solved the initial problem, It did generate a new issue. It generates rows for every record even if there is no schema related data in the record:

When you are referring to a "record". You mean a record in the array?
I am probably misunderstanding it, but how would I specify a schema for the records in the array? I only saw how to do it for a map.

Let me phrase it in another way. Let's say I have a collection school with many documents schoolclass and there is a property students which is of type array. Let's assume I ensure that the records within students are either consistent or the array is empty. And I want schoolclasses without students (or empty students: []) to appear as a row in my schema-view. And just be null for the columns.
Does LEFT JOIN work for me?

EDIT: Adding examples

{
    "school": {
        "id1234": {
            "type": "schoolclass",
            "name": "Class A",
            "students": []
        },

        "id135397": {
            "type": "schoolclass",
            "name": "Class B",
            "students": [
                "John Doe",
                "Jane Dane"
            ]
        }
    }
}

The result should be:
| document_name | document_id | operation | name | students_member | students_index |
| ------------- | ------------- | ----- | - | - | - |
| ... | id1234 | CREATE | Class A | null | null |
| ... | id135397 | CREATE | Class B | John Dow | 0 |
| ... | id135397 | CREATE | Class B | Jane Dane | 1 |

[russellwheatley]

@haynzz I've updated it to be a bit more clear. A record is a Firestore document inserted into BigQuery.

The BigQuery extension does not handle typed arrays, that is a feature request already however.

Use a LEFT-JOIN so your rows are not hidden in the event the students property is an empty array. There is a PR for changing to a LEFT-JOIN that you can track here: #442.

Hope this helps!