In BigQuery, a container of ordered fields each with a type (required) and field name (optional). See .
In Snowflake, can be used to emulate the STRUCT behavior, and SnowConvert handles most implementation differences.
Note: Arguments that represent keys within the OBJECT_CONSTRUCT must be the original names of the target STRUCT. Any name specified within a STRUCT expression body will be replaced with the name found in the target STRUCT. Most of the data pattern examples below contain an example of a name that is replaced by the target name.
CREATE OR REPLACE TABLE test.structTypes
(
COL1 VARIANT /*** SSC-FDM-BQ0009 - STRUCT<INT> CONVERTED TO VARIANT. SOME OF ITS USAGES MIGHT HAVE FUNCTIONAL DIFFERENCES. ***/,
COL2 VARIANT /*** SSC-FDM-BQ0009 - STRUCT<STRING(10)> CONVERTED TO VARIANT. SOME OF ITS USAGES MIGHT HAVE FUNCTIONAL DIFFERENCES. ***/,
COL3 VARIANT /*** SSC-FDM-BQ0009 - STRUCT<STRUCT<INT64, INT64>> CONVERTED TO VARIANT. SOME OF ITS USAGES MIGHT HAVE FUNCTIONAL DIFFERENCES. ***/,
COL4 VARIANT /*** SSC-FDM-BQ0009 - STRUCT<> CONVERTED TO VARIANT. SOME OF ITS USAGES MIGHT HAVE FUNCTIONAL DIFFERENCES. ***/,
COL5 VARIANT /*** SSC-FDM-BQ0009 - STRUCT<INT, INT> CONVERTED TO VARIANT. SOME OF ITS USAGES MIGHT HAVE FUNCTIONAL DIFFERENCES. ***/,
COL7 VARIANT /*** SSC-FDM-BQ0009 - STRUCT<INT, BOOLEAN> CONVERTED TO VARIANT. SOME OF ITS USAGES MIGHT HAVE FUNCTIONAL DIFFERENCES. ***/,
COL8 VARIANT /*** SSC-FDM-BQ0009 - STRUCT<INT, BOOLEAN> CONVERTED TO VARIANT. SOME OF ITS USAGES MIGHT HAVE FUNCTIONAL DIFFERENCES. ***/
);
CREATE OR REPLACE TABLE test.tuple_sample (
COL1 VARIANT /*** SSC-FDM-BQ0009 - STRUCT<INT, INT> CONVERTED TO VARIANT. SOME OF ITS USAGES MIGHT HAVE FUNCTIONAL DIFFERENCES. ***/
);
INSERT INTO test.structTypes (COL1)
SELECT
OBJECT_CONSTRUCT('sc1', 1 :: INT)
UNION ALL
SELECT
OBJECT_CONSTRUCT('sc1', 2 :: INT)
UNION ALL
SELECT
OBJECT_CONSTRUCT('sc1', 3 :: INT)
UNION ALL
SELECT
OBJECT_CONSTRUCT('sc1', 4 :: INT)
UNION ALL
SELECT
OBJECT_CONSTRUCT('sc1', 5 :: INT);
Insert STRING Data Type to STRUCT column
Input Code:
IN -> BigQuery_03.sql
INSERT INTO test.structTypes (COL2) VALUES
(STRUCT('t1')),
(STRUCT<STRING>('t2')),
(STRUCT<sc2 STRING>('t3'));
Output Code:
OUT -> BigQuery_03.sql
INSERT INTO test.structTypes (COL2)
SELECT
OBJECT_CONSTRUCT('sc2', 't1' :: STRING)
UNION ALL
SELECT
OBJECT_CONSTRUCT('sc2', 't2' :: STRING)
UNION ALL
SELECT
OBJECT_CONSTRUCT('sc2', 't3' :: STRING);
INSERT INTO test.structTypes (COL3)
SELECT
OBJECT_CONSTRUCT('sc3', OBJECT_CONSTRUCT('sc31', 1 :: INT, 'sc32', 2 :: INT))
UNION ALL
SELECT
OBJECT_CONSTRUCT('sc3', OBJECT_CONSTRUCT('sc31', 3 :: INT, 'sc32', 4 :: INT))
UNION ALL
SELECT
OBJECT_CONSTRUCT('sc3', OBJECT_CONSTRUCT('sc31', 5 :: INT, 'sc32', 6 :: INT))
UNION ALL
SELECT
OBJECT_CONSTRUCT('sc3', OBJECT_CONSTRUCT('sc31', 7 :: INT, 'sc32', 8 :: INT))
UNION ALL
SELECT
OBJECT_CONSTRUCT('sc3', OBJECT_CONSTRUCT('sc31', 9 :: INT, 'sc32', 10 :: INT));
Insert ARRAY Data Type to STRUCT column
Input Code:
IN -> BigQuery_05.sql
INSERT INTO test.structTypes (COL4) VALUES
(STRUCT([1,2,3,4])),
(STRUCT<sc4 ARRAY<INT64>>(ARRAY[5,6,7])),
(STRUCT<ARRAY<INT64>>([8,9,10,11]));
Output Code:
OUT -> BigQuery_05.sql
INSERT INTO test.structTypes (COL4)
SELECT
OBJECT_CONSTRUCT('sc4', [1,2,3,4] :: ARRAY)
UNION ALL
SELECT
OBJECT_CONSTRUCT('sc4', [5,6,7] :: ARRAY)
UNION ALL
SELECT
OBJECT_CONSTRUCT('sc4', [8,9,10,11] :: ARRAY);
Insert to selected STRUCT columns
Input Code:
IN -> BigQuery_06.sql
INSERT INTO test.structTypes (COL7, COL8) VALUES
(STRUCT(1,true), STRUCT(2,false)),
(STRUCT<INT64, BOOL>(3, false), STRUCT<INT64, BOOL>(4, false)),
(STRUCT<a INT64, b BOOL>(5, true), STRUCT<a INT64, b BOOL>(6, true));
Output Code:
OUT -> BigQuery_06.sql
INSERT INTO test.structTypes (COL7, COL8)
SELECT
OBJECT_CONSTRUCT('sc7', 1 :: INT, 'sc71', true),
OBJECT_CONSTRUCT('sc8', 2 :: INT, 'sc81', false)
UNION ALL
SELECT
OBJECT_CONSTRUCT('sc7', 3 :: INT, 'sc71', false),
OBJECT_CONSTRUCT('sc8', 4 :: INT, 'sc81', false)
UNION ALL
SELECT
OBJECT_CONSTRUCT('sc7', 5 :: INT, 'sc71', true),
OBJECT_CONSTRUCT('sc8', 6 :: INT, 'sc81', true);
Insert to STRUCT column tuple syntax
Translation of tuple syntax values is currently not supported.
INSERT INTO test.tuple_sample
!!!RESOLVE EWI!!! /*** SSC-EWI-BQ0012 - UNABLE TO GENERATE CORRECT OBJECT_CONSTRUCT PARAMETER. SYMBOL INFORMATION COULD NOT BE COLLECTED. ***/!!!
VALUES
((12, 34)),
((56, 78)),
((9, 99)),
((12, 35));
Update STRUCT column
Input Code:
IN -> BigQuery_08.sql
UPDATE test.structTypes
SET col1 = STRUCT(100 AS number)
WHERE col1.sc1 = 4;
Output Code:
OUT -> BigQuery_08.sql
UPDATE test.structTypes
SET col1 = OBJECT_CONSTRUCT('sc1', 100 :: INT)
WHERE col1:sc1 = 4;
Update STRUCT column field
Input Code:
IN -> BigQuery_09.sql
UPDATE test.structTypes
SET col3 = STRUCT(STRUCT(80,90))
WHERE col3.sc3.sc31 = 20;
Output Code:
OUT -> BigQuery_09.sql
UPDATE test.structTypes
SET col3 = OBJECT_CONSTRUCT('sc3', OBJECT_CONSTRUCT('sc31', 80 :: INT, 'sc32', 90 :: INT))
WHERE col3:sc3:sc31 = 20;
Select from STRUCT column
Input Code:
IN -> BigQuery_10.sql
SELECT COL3.sc3 FROM test.structTypes;
SELECT COL3.sc3.sc32 FROM test.structTypes;
SELECT COL4.sc4 FROM test.structTypes WHERE COL4.sc4 IS NOT NULL;
Output Code:
OUT -> BigQuery_10.sql
SELECT COL3:sc3
FROM
test.structTypes;
SELECT COL3:sc3:sc32
FROM
test.structTypes;
SELECT COL4:sc4
FROM
test.structTypes
WHERE COL4:sc4 IS NOT NULL;
Select from STRUCT column tuple syntax
Input Code:
IN -> BigQuery_11.sql
SELECT *
FROM test.tuple_sample
WHERE (COL1.Key1, COL1.Key2) IN ((12, 34), (56, 78));
SELECT STRUCT<x ARRAY<INT64>, y INT64>(COL4.sc4, COL1.sc1)
FROM test.structTypes
WHERE COL1.sc1 IS NOT NULL;
Output Code:
OUT -> BigQuery_11.sql
SELECT *
FROM
test.tuple_sample
WHERE (COL1:Key1, COL1:Key2) IN ((12, 34), (56, 78));
SELECT
OBJECT_CONSTRUCT('x', COL4:sc4 :: ARRAY, 'y', COL1:sc1 :: INT)
FROM
test.structTypes
WHERE COL1:sc1 IS NOT NULL;
Create a view using an anonymous STRUCT definition
Input Code:
IN -> BigQuery_12.sql
CREATE OR REPLACE TABLE project-test.mydataset.sourcetable (
id STRING,
payload JSON
);
CREATE VIEW project-test.mydataset.myview AS
SELECT
id,
STRUCT(
payload.user_id AS user_id,
STRUCT(
JSON_VALUE(payload, '$.details.ip_address') AS ip_address,
JSON_VALUE(payload, '$.details.item_id') AS item_id,
SAFE_CAST(JSON_VALUE(payload, '$.details.quantity') AS INT64) AS quantity,
SAFE_CAST(JSON_VALUE(payload, '$.details.price') AS FLOAT64) AS price,
JSON_VALUE(payload, '$.details.text') AS text
) AS details
) AS structured_payload
FROM project-test.mydataset.sourcetable;
Output Code:
OUT -> BigQuery_12.sql
CREATE OR REPLACE TABLE "project-test".mydataset.sourcetable (
id STRING,
payload VARIANT
));
CREATE VIEW "project-test".mydataset.myview
AS
SELECT
id,
OBJECT_CONSTRUCT('user_id',
payload:user_id, 'details', OBJECT_CONSTRUCT('ip_address', JSON_EXTRACT_PATH_TEXT(payload, 'details.ip_address'), 'item_id', JSON_EXTRACT_PATH_TEXT(payload, 'details.item_id'), 'quantity', TRY_CAST(JSON_EXTRACT_PATH_TEXT(payload, 'details.quantity') AS INT), 'price', TRY_CAST(JSON_EXTRACT_PATH_TEXT(payload, 'details.price') AS FLOAT), 'text', JSON_EXTRACT_PATH_TEXT(payload, 'details.text'))) AS structured_payload
FROM
"project-test".mydataset.sourcetable;
STRUCT column comparison expressions
BigQuery comparison operations for Structs compare value to value, ignoring the key if it exists, while Snowflake comparison operations for Objects compare both, value and key. This may cause that some comparisons return a different result.
Input Code:
IN -> BigQuery_13.sql
SELECT * FROM test.structTypes WHERE COL1 NOT IN (COL2);
SELECT * FROM test.structTypes WHERE COL1 <> (COL2);
SELECT * FROM test.structTypes WHERE COL1 != (COL2);
Output Code:
OUT -> BigQuery_13.sql
SELECT * FROM
test.structTypes
--** SSC-FDM-BQ0008 - WHERE CLAUSE REFERENCES A COLUMN OF STRUCT TYPE. COMPARISON OPERATIONS MAY PRODUCE DIFFERENT RESULTS IN SNOWFLAKE. **
WHERE COL1 NOT IN (COL2);
SELECT * FROM
test.structTypes
--** SSC-FDM-BQ0008 - WHERE CLAUSE REFERENCES A COLUMN OF STRUCT TYPE. COMPARISON OPERATIONS MAY PRODUCE DIFFERENT RESULTS IN SNOWFLAKE. **
WHERE COL1 <> (COL2);
SELECT * FROM
test.structTypes
--** SSC-FDM-BQ0008 - WHERE CLAUSE REFERENCES A COLUMN OF STRUCT TYPE. COMPARISON OPERATIONS MAY PRODUCE DIFFERENT RESULTS IN SNOWFLAKE. **
WHERE COL1 != (COL2);
Related EWIs
: Struct converted to VARIANT. Some of its usages might have functional differences.
: Unable to generate correct OBJECT_CONSTRUCT parameter.
: Where clause references a column of STRUCT type.
