CODEWITHSUNDEEP
snowflake-cloud-data-platform
Vaibhav Sharma
Vaibhav Sharma

Reputation: 1742

How to insert JSON data into a column of a Snowflake Datawarehouse table?

I am trying to write an SQL Query to upload JSON data into Snowflake DB Table.

The query I have written looks like:

insert into xyz_table(id, json_column) values (1, '{
  "first_name": "John",
  "last_name": "Corner",
  "createddate": "2019-07-02T10:01:30+00:00",
  "type": "Owner",
  "country": {
    "code": "US",
    "name": "United States"
  }
}');

And I am getting below error

SQL compilation error: Expression type does not match column data type, expecting VARIANT but got VARCHAR(182) for column CANONICAL_JSON

Please let me know, how can we insert JSON data using SQL query in Snowflake DB Table

Upvotes: 8

Views: 21717

Answers (2)

Antriksha Somani
Antriksha Somani

Reputation: 111

There can be another way(shorter) round in case you are creating a query builder or something:

INSERT INTO xyz_table (id, json_column)
SELECT
  101 ,
  PARSE_JSON('[{"id": 9, "name": "Member"}, {"id": 10, "name": "Volunteer"}]');

Upvotes: 3

Vaibhav Sharma
Vaibhav Sharma

Reputation: 1742

I have got the solution of it:

The query can be written as:

insert into xyz_table(id, json_column) select 1, parse_json($${
  "first_name": "John",
  "last_name": "Corner",
  "createddate": "2019-07-02T10:01:30+00:00",
  "type": "Owner",
  "country": {
    "code": "US",
    "name": "United States"
  }
}$$);

Upvotes: 18

Related Questions