A library to handle JSON with snowflake-sqlalchemy.
Project description
snowflake-sqlalchemy-json
This is a library to handle JSON data in snowflake-sqlalchemy.
Installation
$ pip install snowflake-sqlalchemy-json
Usage
Note that the current version support SELECT of JSON columns, but it does not support INSERT or UPDATE of them.
This library supports access to elements in JSON columns.
You can access JSON columns as follows:
- Define a column as
JSON
type.
Though the actual column type isVARIANT
, you have to useJSON
instead. - You can refer to elements in the column like
dict
.
IfBook
has a JSON column,json_data
, you can refer to an element in the column asBook.json_data["key"]
. - You can also use
func.flatten
function to flatten values in a JSON column.
Please refer to the following example.
import snowflake_sqlalchemy_json
from sqlalchemy import Column, Integer, JSON, String, func, select
from sqlalchemy.orm import declarative_base, DeclarativeMeta
from sqlalchemy.sql import quoted_name
# You have to call this function to enable `func.flatten`.
snowflake_sqlalchemy_json.register_json_handler()
Base: DeclarativeMeta = declarative_base()
class Book(Base):
__tablename__ = quoted_name("database_name.schema_name.books", False)
id = Column(Integer, primary_key=True)
title = Column(String(255))
json_data = Column(JSON)
editors = func.flatten(Book.json_data["editors"]).lateral()
query = select(
Book.title,
editors.c.value["name"],
).select_from(Book).join(
editors,
True,
).where(
editors.c.value["type"] == "chief",
).order_by(editors.c.value["name"].desc())
query
in the above example generates the following SQL.
SELECT database_name.schema_name.books.title, GET(anon_2.value, 'name') AS anon_1
FROM database_name.schema_name.books JOIN LATERAL flatten(INPUT => (GET(database_name.schema_name.books.json_data, 'editors'))) AS anon_2 ON true
WHERE GET(anon_2.value, 'type') = 'chief' ORDER BY GET(anon_2.value, 'name') DESC
Project details
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distributions
No source distribution files available for this release.See tutorial on generating distribution archives.
Built Distribution
File details
Details for the file snowflake_sqlalchemy_json-1.1.2-py3-none-any.whl
.
File metadata
- Download URL: snowflake_sqlalchemy_json-1.1.2-py3-none-any.whl
- Upload date:
- Size: 4.8 kB
- Tags: Python 3
- Uploaded using Trusted Publishing? No
- Uploaded via: twine/4.0.0 CPython/3.9.4
File hashes
Algorithm | Hash digest | |
---|---|---|
SHA256 | 148da71158e52ed7a9a2323e1cbd2b6c52ecc12ae776f3b4d6c3be9ae6ef900f |
|
MD5 | c221e70e10ade3261ec089f32170ddf9 |
|
BLAKE2b-256 | 2b82d41f367e4fe91dad493819560128f16daa18fd417dda623cad8c55d3d1a5 |