Skip to main content

A library to handle JSON with snowflake-sqlalchemy.

Project description

PyPI version build

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:

  1. Define a column as JSON type.
    Though the actual column type is VARIANT, you have to use JSON instead.
  2. You can refer to elements in the column like dict.
    If Book has a JSON column, json_data, you can refer to an element in the column as Book.json_data["key"].
  3. 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

File hashes

Hashes for snowflake_sqlalchemy_json-1.1.2-py3-none-any.whl
Algorithm Hash digest
SHA256 148da71158e52ed7a9a2323e1cbd2b6c52ecc12ae776f3b4d6c3be9ae6ef900f
MD5 c221e70e10ade3261ec089f32170ddf9
BLAKE2b-256 2b82d41f367e4fe91dad493819560128f16daa18fd417dda623cad8c55d3d1a5

See more details on using hashes here.

Supported by

AWS AWS Cloud computing and Security Sponsor Datadog Datadog Monitoring Fastly Fastly CDN Google Google Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Sentry Sentry Error logging StatusPage StatusPage Status page