How can I query and filter data in this case?-CodePudding

This function was designed to create the database that is in the images.

exports.processosjudiciais = functions.https.onRequest(async (request, response): Promise<any> => {

    const jwtClient = new google.auth.JWT({
        email: serviceAccount.client_email,
        key: serviceAccount.private_key,
        scopes: ['https://www.googleapis.com/auth/spreadsheets']
    })

    await jwtClient.authorize()

    const { data } = await sheets.spreadsheets.values.get({
        auth: jwtClient,
        spreadsheetId: 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX',
        range: `Processos judiciais!A11664:E11667`
    })
    
    const updatePromises = new Array()
    data.values?.forEach(row => {
        const [processoBE, autoresBE, documentosDosautores, arbitramentoDeHonoráriosBE, valorDaCausa] = row
        firestore.collection("Processos judiciais").doc(processoBE).set({
            processoBE, autoresBE, documentosDosautores, valorDaCausa
        })
        arbitramentoDeHonoráriosBE.split("; ").forEach((v: string) => {
            updatePromises.push(
                firestore.collection("Processos judiciais").doc(processoBE).collection('fee-arbitrations - Base de Execução').doc(v).set({
                    arbitramentoDeHonoráriosBE: v,
                    processoBE,
                })
            )
        })
    })
    return await Promise.all(updatePromises)
})

I need to compare the data that I highlighted with the arrow in red, with the values that are in the google spreadsheet (I also highlighted in red in the image). How can I make this comparison? From the code I made, if I make the comparison only with the first "where", I can extract a result. I need to do the comparison with the two "where". What am I doing wrong? Why can't I make this comparison?

import * as functions from 'firebase-functions'
import { google } from 'googleapis'
import { initializeApp } from 'firebase-admin/app'
const serviceAccount = require('../sheets_updater_service_account.json')
const sheets = google.sheets('v4')
import { getFirestore  } from "firebase-admin/firestore"
initializeApp()
const firestore = getFirestore()


module.exports.readAndUpdateAdministrativeSheet = functions.https.onRequest(async (request, response) => {

    // =========================== AUTENTICAÇÃO FIREBASE ===================================
    const jwtClient = new google.auth.JWT({
        email: serviceAccount.client_email,
        key: serviceAccount.private_key,
        scopes: ['https://www.googleapis.com/auth/spreadsheets']
    })

    await jwtClient.authorize()


    // ================= CONEXÃO COM A PLANILHA CRIAÇÃO DE FILTROS =========================
    const { data } = await sheets.spreadsheets.values.get({
        auth: jwtClient,
        spreadsheetId: '1bGJGQInmGmI-ODHM5hSZtQdvbPFVzWT70oPD6mTxcmU',
        range: `Listagem de pagamento!A2:X6`,
    })

    // ========= CRIAÇÃO DE BANCO DE DADOS DA COLEÇÃO LISTAGEM DE PAGAMENTO ================
    const generateDuplicities = data.values!.map(async row => {
        const [idade, nome, cpf, cpf_x, numeroRequerimento, arbitramentoHonorários,
            valorArbitrado, valorDeferido, valorComplementar, status, resultado, codigoBanco,
            banco, agencia, conta, dataDoRequerimento, dataRequerimento, dataStatus,
            comarca, vara, ato, assistidos, email, telefone] = row
        firestore.collection("Listagem de pagamento").doc(numeroRequerimento).set({
            idade, nome, cpf, cpf_x, numeroRequerimento, arbitramentoHonorários,
            valorArbitrado, valorDeferido, valorComplementar, status, resultado, codigoBanco,
            banco, agencia, conta, dataDoRequerimento, dataRequerimento, dataStatus, comarca, vara, ato,
            assistidos, email, telefone
        })
const duplicitiesWithJudicialCharges = new Array()
        const resultduplicitiesWithJudicialCharges = firestore.collection("Processos judiciais") //1VgkQFGZCKvINMLtnVLIeC0tQhAjxeNk92PQN5QKLq-Q (Base de dados de execuções)
            .where("arbitramentoDeHonoráriosBE", "array-contains", arbitramentoHonorários) // Arbitramento de Honorários => Planilha Base de dados de execuções  /  Arbitramento de Honorários => Planilha de Criação de filtros
            .where("documentosDosautores", "==", cpf) // documentosDosautores => Planilha Base de dados de execuções  /  cpf => Planilha de Criação de filtros
        const resDuplicitiesWithJudicialCharges = await resultduplicitiesWithJudicialCharges.get()        
        if (resDuplicitiesWithJudicialCharges.size) {
            resDuplicitiesWithJudicialCharges.forEach(doc => {
                if (resDuplicitiesWithJudicialCharges.size == 1) {
                    functions.logger.log("Foi encontrada "   `${resDuplicitiesWithJudicialCharges.size} `   "duplicidade judicial referente a pessoa "   `${nome}.`)
                    functions.logger.log(doc.id) // NÃO MEXER
                    functions.logger.log(doc.data()) //NÃO MEXER
                    
                    duplicitiesWithJudicialCharges.push(`${'arbitramentoHonorários'}: ${arbitramentoHonorários}`, `${'nome'}: ${nome}`, `${'processoBE'}: ${doc.data().processoBE}`)
                    functions.logger.log(duplicitiesWithJudicialCharges)//NÃO MEXER
                } else if (resDuplicitiesWithJudicialCharges.size > 1) {
                    functions.logger.log("Foram encontradas "   `${resDuplicitiesWithJudicialCharges.size} `   "duplicidades Judicial referente a pessoa "   `${nome}.`)
                }
            })
        }    
        else if (resDuplicitiesWithJudicialCharges.size <= 0){
            functions.logger.log('Não há duplicidades judicial nesta lista')
        }
 })
    await Promise.all(generateDuplicities)
})

Can anyone tell me what I'm doing wrong?

CodePudding user response：

On this line of code:

const resultduplicitiesWithJudicialCharges = firestore.collection("Processos judiciais") //1VgkQFGZCKvINMLtnVLIeC0tQhAjxeNk92PQN5QKLq-Q (Base de dados de execuções)
.where("arbitramentoDeHonoráriosBE", "array-contains", arbitramentoHonorários) // Arbitramento de Honorários => Planilha Base de dados de execuções  /  Arbitramento de Honorários => Planilha de Criação de filtros
.where("documentosDosautores", "==", cpf)

There are some issues I want to point out.

Your Firestore Data Structure has a nested collection which means that you have a parent collection and a sub-collection within a document based on your given image:
You're only querying documents inside the Processos judiciais collection so your query cannot reach documents within your sub-collection.
array-contains can only be used in fields that are array in type which in the image shown was a string.
If the variable arbitramentoHonorários is an array, you should use the in operator instead. If it's a string then you should use the == operator.
The only valid query on the given code above is this one: .where("documentosDosautores", "==", cpf).

To be able to query the collection and sub-collection, you must first query the collection and loop through all the matched data and call another query to query document's subcollection and push it to the initialized array. See code below:

const resultduplicitiesWithJudicialCharges = db.collection('Processos judiciais').where('documentosDosautores', '==', cpf);
const duplicitiesWithJudicialCharges = [];

resultduplicitiesWithJudicialCharges.get().then((querySnapshot) => {
  // Loop through all the matched result from the parent collection query.
  querySnapshot.forEach((parentDoc) => {
    // Logs documents from the parent collection.
    console.log(childDoc.id, " => ", doc.data());
    // Queries the subcollection where in `parentDoc.ref` are the documents from the result of 1st query.
    parentDoc.ref.collection(<'YOUR-SUBCOLLECTION-NAME'>).where('arbitramentoDeHonoráriosBE', '==', arbitramentoHonorários).get().then((querySnapshot) => {
      // Loop through all matched result from the subcollection query for each document matched from the parent query.
      querySnapshot.forEach((childDoc) => {
          // Logs documents from the subcollection.
          console.log(childDoc.id, " => ", doc.data());
          // Push data to the initialized array. Also used the `parentDoc.data()` that we fetched on the previous query.
          duplicitiesWithJudicialCharges.push(`${'arbitramentoHonorários'}: ${arbitramentoHonorários}`, `${'nome'}: ${nome}`, `${'processoBE'}: ${parentDoc.data().processoBE}`)
      });
    });
  });
});

Added some comments on the code snippet above for a better explanation.

For more relevant information, you may check out this documentations: