CODEWITHSUNDEEP
google-bigquerynodejs-bigquery
Reza
Reza

Reputation: 19913

Named parameter type for Array of Struct in biqquery

I had a code like below

        const changes = dto.data.map(d => `('${d.findingId}', '${d.status}', ${d.comment ? `'${d.comment}'` : null})`).join(',')

        const query = `
            MERGE \`${projectId}.dlp.result-status\`
            USING UNNEST([struct<findingId STRING, status STRING, comment STRING> ${changes} ]) changes
            ON \`${projectId}.dlp.result-status\`.findingId = changes.findingId
            WHEN NOT MATCHED THEN
                INSERT (findingId, status, modifiedAt, comment ) VALUES (changes.findingId, changes.status, '${now}', changes.comment)
            WHEN MATCHED THEN
                UPDATE SET 
                    \`${projectId}.dlp.result-status\`.status = changes.status, 
                    \`${projectId}.dlp.result-status\`.comment = changes.comment
            ;
        `;

        const options = {
            query: query,
            // Location must match that of the dataset(s) referenced in the query.
            location: 'US'
        };

but it was vulnerable to SQL injection, so I changed my code to use parameters

        const changes = dto.data.map(d => ({ findingId: d.findingId, status: d.status, comment: d.comment }))

        const query = `
            MERGE \`${projectId}.dlp.result-status\`
            USING UNNEST(@changes) changes
            ON \`${projectId}.dlp.result-status\`.findingId = changes.findingId
            WHEN NOT MATCHED THEN
                INSERT (findingId, status, modifiedAt, comment ) VALUES (changes.findingId, changes.status, @now, changes.comment)
            WHEN MATCHED THEN
                UPDATE SET 
                    \`${projectId}.dlp.result-status\`.status = changes.status, 
                    \`${projectId}.dlp.result-status\`.comment = changes.comment
            ;
        `;

        const options: Query = {
            query: query,
            // Location must match that of the dataset(s) referenced in the query.
            location: 'US',
            params: { now: now, changes: changes },
            types: {
                now: 'STRING',
                // changes: [{ 'findingId': 'STRING', 'status': 'STRING', 'comment': 'STRING' }]
                // changes: ['STRUCT']
            }
        };

so important change is changing changes variable from concatenated string to array of objects and changing USING UNNEST([struct<findingId STRING, status STRING, comment STRING> ${changes} ]) changes to USING UNNEST(@changes) changes

When I ran this, I get this error

Parameter types must be provided for null values via the 'types' field in query options.

So I tried to add types, now I am stuck in how to add type for Array of struct, I tried these but no lock

changes: [{ 'findingId': 'STRING', 'status': 'STRING', 'comment': 'STRING' }]
changes: ['STRUCT']

ps. This is the type definition on Query enter image description here

Upvotes: 0

Views: 844

Answers (1)

Reza
Reza

Reputation: 19913

Seems this is only typing (ts) issue, I made it like this and worked (added as any)

        const options: Query = {
            query: query,query.
            location: 'US',
            params: { now: now, changes: changes },
            types: {
                now: 'STRING',
                changes: [{ findingId: 'STRING', status: 'STRING', comment: 'STRING' }] as any
            }
        };

Upvotes: 2

Related Questions