[](https://badge.fury.io/py/snowflake-sqlalchemy-json)
[](https://github.com/masamitsu-murase/snowflake-sqlalchemy-json/actions/workflows/ci.yml)
# snowflake-sqlalchemy-json
This is a library to handle JSON data in [snowflake-sqlalchemy](https://pypi.org/project/snowflake-sqlalchemy/).
## Installation
```bash
$ 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.
1. 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"]`.
1. You can also use [`func.flatten` function](https://docs.snowflake.com/en/sql-reference/functions/flatten.html) to flatten values in a JSON column.
Please refer to the following example.
```python
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.
```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
```
Raw data
{
"_id": null,
"home_page": "https://github.com/masamitsu-murase/snowflake-sqlalchemy-json",
"name": "snowflake-sqlalchemy-json",
"maintainer": "",
"docs_url": null,
"requires_python": ">=3.7, <4",
"maintainer_email": "",
"keywords": "snowflake sqlalchemy",
"author": "Masamitsu MURASE",
"author_email": "masamitsu.murase@gmail.com",
"download_url": "",
"platform": null,
"description": "[](https://badge.fury.io/py/snowflake-sqlalchemy-json)\n[](https://github.com/masamitsu-murase/snowflake-sqlalchemy-json/actions/workflows/ci.yml)\n\n# snowflake-sqlalchemy-json\n\nThis is a library to handle JSON data in [snowflake-sqlalchemy](https://pypi.org/project/snowflake-sqlalchemy/).\n\n## Installation\n\n```bash\n$ pip install snowflake-sqlalchemy-json\n```\n\n## Usage\n\nNote that the current version support SELECT of JSON columns, but it does not support INSERT or UPDATE of them.\n\nThis library supports access to elements in JSON columns. \nYou can access JSON columns as follows:\n\n1. Define a column as `JSON` type. \n Though the actual column type is `VARIANT`, you have to use `JSON` instead.\n1. You can refer to elements in the column like `dict`. \n If `Book` has a JSON column, `json_data`, you can refer to an element in the column as `Book.json_data[\"key\"]`.\n1. You can also use [`func.flatten` function](https://docs.snowflake.com/en/sql-reference/functions/flatten.html) to flatten values in a JSON column. \n Please refer to the following example.\n\n```python\nimport snowflake_sqlalchemy_json\nfrom sqlalchemy import Column, Integer, JSON, String, func, select\nfrom sqlalchemy.orm import declarative_base, DeclarativeMeta\nfrom sqlalchemy.sql import quoted_name\n\n# You have to call this function to enable `func.flatten`.\nsnowflake_sqlalchemy_json.register_json_handler()\n\nBase: DeclarativeMeta = declarative_base()\n\n\nclass Book(Base):\n __tablename__ = quoted_name(\"database_name.schema_name.books\", False)\n id = Column(Integer, primary_key=True)\n title = Column(String(255))\n json_data = Column(JSON)\n\n\neditors = func.flatten(Book.json_data[\"editors\"]).lateral()\nquery = select(\n Book.title,\n editors.c.value[\"name\"],\n).select_from(Book).join(\n editors,\n True,\n).where(\n editors.c.value[\"type\"] == \"chief\",\n).order_by(editors.c.value[\"name\"].desc())\n```\n\n`query` in the above example generates the following SQL.\n\n```sql\nSELECT database_name.schema_name.books.title, GET(anon_2.value, 'name') AS anon_1\nFROM database_name.schema_name.books JOIN LATERAL flatten(INPUT => (GET(database_name.schema_name.books.json_data, 'editors'))) AS anon_2 ON true\nWHERE GET(anon_2.value, 'type') = 'chief' ORDER BY GET(anon_2.value, 'name') DESC\n```\n\n\n",
"bugtrack_url": null,
"license": "MIT",
"summary": "A library to handle JSON with snowflake-sqlalchemy.",
"version": "1.1.2",
"project_urls": {
"Bug Reports": "https://github.com/masamitsu-murase/snowflake-sqlalchemy-json/issues",
"Homepage": "https://github.com/masamitsu-murase/snowflake-sqlalchemy-json",
"Source": "https://github.com/masamitsu-murase/snowflake-sqlalchemy-json"
},
"split_keywords": [
"snowflake",
"sqlalchemy"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "2b82d41f367e4fe91dad493819560128f16daa18fd417dda623cad8c55d3d1a5",
"md5": "c221e70e10ade3261ec089f32170ddf9",
"sha256": "148da71158e52ed7a9a2323e1cbd2b6c52ecc12ae776f3b4d6c3be9ae6ef900f"
},
"downloads": -1,
"filename": "snowflake_sqlalchemy_json-1.1.2-py3-none-any.whl",
"has_sig": false,
"md5_digest": "c221e70e10ade3261ec089f32170ddf9",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.7, <4",
"size": 4796,
"upload_time": "2023-10-09T07:00:24",
"upload_time_iso_8601": "2023-10-09T07:00:24.073325Z",
"url": "https://files.pythonhosted.org/packages/2b/82/d41f367e4fe91dad493819560128f16daa18fd417dda623cad8c55d3d1a5/snowflake_sqlalchemy_json-1.1.2-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2023-10-09 07:00:24",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "masamitsu-murase",
"github_project": "snowflake-sqlalchemy-json",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"lcname": "snowflake-sqlalchemy-json"
}
JSON
