{ "info": { "author": "Anthony SKORSKI", "author_email": "skorski.anthony+pg_jsonb_flattener@gmail.com", "bugtrack_url": null, "classifiers": [ "Development Status :: 3 - Alpha", "Intended Audience :: Developers", "License :: OSI Approved :: MIT License", "Operating System :: OS Independent", "Programming Language :: Python :: 2", "Programming Language :: Python :: 3", "Topic :: Database" ], "description": ".. |master_pipeline| image:: https://gitlab.skor.ski/askorski/pg_jsonb_flattener/badges/master/pipeline.svg\n.. |master_coverage| image:: https://gitlab.skor.ski/askorski/pg_jsonb_flattener/badges/master/coverage.svg\n.. |develop_pipeline| image:: https://gitlab.skor.ski/askorski/pg_jsonb_flattener/badges/develop/pipeline.svg\n.. |develop_coverage| image:: https://gitlab.skor.ski/askorski/pg_jsonb_flattener/badges/develop/coverage.svg\n\n\npg_jsonb_flattener\n##################\n\n\nThe `pg_jsonb_flattener` is a tool to ease selection of Postgresql table which\ncontains JSONB typed fields.\n\nProvided with a description of the data (a sort of jsonschema) contained in a\nJSONB field, it is able to build a selectable sqlalchemy expression which can\nbe used as a plain Postgresql table for SELECT queries.\n\nStatus\n******\n\n+---------+---------------------+---------------------+\n| branch | CI | Coverage |\n+=========+=====================+=====================+\n| master | |master_pipeline| | |master_coverage| |\n+---------+---------------------+---------------------+\n| develop | |develop_pipeline| | |develop_coverage| |\n+---------+---------------------+---------------------+\n\nUsage\n*****\n\nThe tool is used by instantiating a `JsonbTable` providing it:\n\n* a sqlaclehmy table description as the only positionnal argument\n* a *JSON data description* for each *JSONB field* to flatten as named arguments\n where the argument name is the *JSONB field* name and the argument value is\n the *JSON data description*.\n\nThe code below illustrates how the tool can be used to obtain a\n`flat selectable` from a data description written in a JSON file.\n\n.. code-block:: python\n\n from sqlalchemy import create_engine, MetaData, Table\n from pg_jsonb_flattener import JsonbTable\n\n ENGINE = create_engine('some database URL')\n METADATA = MetaData()\n ONE_JSON_FIELD_TABLE = Table(\n 'one_json_field_table', METADATA,\n Column('field_one', JSONB)\n )\n DATA_DESCRIPTION = json.load('path/to/data_description.json')\n\n jsonb_table = JsonbTable(ONE_JSON_FIELD_TABLE, field_one=DATA_DESCRIPTION)\n\n flat_selectable = jsonb_table.get_query()\n\n with engine.connect() as conn:\n result = conn.execute(flat_selectable).fetchall()\n\nThen, the ``flat_selectable`` variable contains a selectable expression which acts\nas a table where the JSONB `field_one` field has been replaced by several\nscalar typed fields whose names are build from the original JSONB field name\nand the path of the data in the data structure, with a double undercore as\nseparator.\n\nYou can execute directly the ``flat_selectable`` to inspect what it contains\n(this is what is done in the example to get the ``result`` variable) or use it as\na plain table to build more complex queries.\n\nSee below examples for more details.\n\n\nSimple types\n============\n\nGiven the following data description::\n\n {\n \"type\": \"object\",\n \"properties\": {\n \"integer\": {\"type\": \"integer\"},\n \"number\": {\"type\": \"number\"},\n \"string\": {\"type\": \"string\"}\n \"boolean\": {\"type\": \"boolean\"}\n }\n }\n\n``flat_selectable`` would provide the following fields:\n\n* ``field_one__integer``\n* ``field_one__number``\n* ``field_one__string``\n* ``field_one__boolean``\n\nGiven the following JSON data in one record::\n\n {\n \"integer\": 42,\n \"number\": 3.141592,\n \"string\": \"pouet\",\n \"boolean\": false\n }\n\nThe ``result`` variable would contain this:\n\n+-----------------------+----------------------+----------------------+----------------------+\n| field_one__integer | field_one__number | field_one__string | field_one__boolean |\n+=======================+======================+======================+======================+\n| 42 | 3.141592 | pouet | false |\n+-----------------------+----------------------+----------------------+----------------------+\n\n\nSimple objects\n==============\n\nGiven the following data description::\n\n {\n \"type\": \"object\",\n \"properties\": {\n \"object\": {\n \"type\": \"object\",\n \"properties\": {\n \"obj_int\": {\"type\": \"integer\"},\n \"obj_num\": {\"type\": \"number\"},\n \"obj_str\": {\"type\": \"string\"}\n }\n }\n }\n }\n\n``flat_selectable`` would provide the following fields:\n\n* ``field_one__object__obj_int``\n* ``field_one__object__obj_num``\n* ``field_one__object__obj_str``\n\nGiven the following JSON data in one record::\n\n {\n \"object\": {\n \"obj_int\": 42,\n \"obj_num\": 3.141592,\n \"obj_str\": \"pouet\"\n }\n }\n\nThe ``result`` variable would contain this:\n\n+----------------------------------+----------------------------------+----------------------------------+\n| field_one__object__obj_int | field_one__object__obj_num | field_one__object__obj_str |\n+==================================+==================================+==================================+\n| 42 | 3.141592 | pouet |\n+----------------------------------+----------------------------------+----------------------------------+\n\n\nSimple arrays\n=============\n\nGiven the following data description::\n\n {\n \"type\": \"object\",\n \"properties\": {\n \"int_arr\": {\n \"type\": \"array\",\n \"items\": {\"type\": \"integer\"}\n },\n \"num_arr\": {\n \"type\": \"array\",\n \"items\": {\"type\": \"number\"}\n },\n \"str_arr\": {\n \"type\": \"array\",\n \"items\": {\"type\": \"string\"}\n }\n }\n }\n\n``flat_selectable`` would provide the following fields:\n\n* ``field_one__int_arr__item``\n* ``field_one__int_arr__item__index``\n* ``field_one__num_arr__item``\n* ``field_one__num_arr__item__index``\n* ``field_one__str_arr__item``\n* ``field_one__str_arr__item__index``\n\n.. note::\n\n As you can see each array field is suffixed by an `item` part and each array\n data is identified by an index which allows to retrieve the order of the\n data in the JSONB array.\n\nGiven the following JSON data in one record::\n\n {\n \"int_arr\": [42, 77, 1664],\n \"num_arr\": [3.141592, 1.618034, 37.2],\n \"str_arr\": [\"pouet\", \"foo\", \"bar\"]\n }\n\nThe ``result`` variable would contain this:\n\n+--------------------------+---------------------------------+--------------------------+---------------------------------+--------------------------+---------------------------------+\n| field_one__int_arr__item | field_one__int_arr__item__index | field_one__num_arr__item | field_one__num_arr__item__index | field_one__str_arr__item | field_one__str_arr__item__index |\n+==========================+=================================+==========================+=================================+==========================+=================================+\n| 42 | 1 | NULL | NULL | NULL | NULL |\n+--------------------------+---------------------------------+--------------------------+---------------------------------+--------------------------+---------------------------------+\n| 77 | 2 | NULL | NULL | NULL | NULL |\n+--------------------------+---------------------------------+--------------------------+---------------------------------+--------------------------+---------------------------------+\n| 1664 | 3 | NULL | NULL | NULL | NULL |\n+--------------------------+---------------------------------+--------------------------+---------------------------------+--------------------------+---------------------------------+\n| NULL | NULL | 3.141592 | 1 | NULL | NULL |\n+--------------------------+---------------------------------+--------------------------+---------------------------------+--------------------------+---------------------------------+\n| NULL | NULL | 1.618034 | 2 | NULL | NULL |\n+--------------------------+---------------------------------+--------------------------+---------------------------------+--------------------------+---------------------------------+\n| NULL | NULL | 37.2 | 3 | NULL | NULL |\n+--------------------------+---------------------------------+--------------------------+---------------------------------+--------------------------+---------------------------------+\n| NULL | NULL | NULL | NULL | pouet | 1 |\n+--------------------------+---------------------------------+--------------------------+---------------------------------+--------------------------+---------------------------------+\n| NULL | NULL | NULL | NULL | foo | 2 |\n+--------------------------+---------------------------------+--------------------------+---------------------------------+--------------------------+---------------------------------+\n| NULL | NULL | NULL | NULL | bar | 3 |\n+--------------------------+---------------------------------+--------------------------+---------------------------------+--------------------------+---------------------------------+\n\n\nArray of objects\n================\n\nGiven the following data description::\n\n {\n \"type\": \"object\",\n \"properties\": {\n \"array\": {\n \"type\": \"array\",\n \"items\": {\n \"type\": \"object\",\n \"properties\": {\n \"obj_int\": {\"type\": \"integer\"},\n \"obj_num\": {\"type\": \"number\"},\n \"obj_str\": {\"type\": \"string\"}\n }\n }\n }\n }\n }\n\n``flat_selectable`` would provide the following fields:\n\n* ``field_one__array__item__index``\n* ``field_one__array__item__obj_int``\n* ``field_one__array__item__obj_num``\n* ``field_one__array__item__obj_str``\n\nGiven the following JSON data in one record::\n\n {\n \"array\": [\n {\n \"obj_int\": 42,\n \"obj_num\": 3.141592,\n \"obj_str\": \"pouet\"\n },\n {\n \"obj_int\": 77,\n \"obj_num\": 1.618034,\n \"obj_str\": \"toto\"\n }\n ]\n }\n\nThe ``result`` variable would contain this:\n\n+-------------------------------+---------------------------------+---------------------------------+---------------------------------+\n| field_one__array__item__index | field_one__array__item__obj_int | field_one__array__item__obj_num | field_one__array__item__obj_str |\n+===============================+=================================+=================================+=================================+\n| 1 | 42 | 3.141592 | pouet |\n+-------------------------------+---------------------------------+---------------------------------+---------------------------------+\n| 2 | 77 | 1.618034 | toto |\n+-------------------------------+---------------------------------+---------------------------------+---------------------------------+\n\n\nArrays in object\n================\n\nGiven the following data description::\n\n {\n \"type\": \"object\",\n \"properties\": {\n \"object\": {\n \"type\": \"object\",\n \"properties\": {\n \"int_arr\": {\n \"type\": \"array\",\n \"items\": {\"type\": \"integer\"}\n },\n \"num_arr\": {\n \"type\": \"array\",\n \"items\": {\"type\": \"number\"}\n },\n \"str_arr\": {\n \"type\": \"array\",\n \"items\": {\"type\": \"string\"}\n }\n }\n }\n }\n }\n\n``flat_selectable`` would provide the following fields:\n\n* ``field_one__object__int_arr__item``\n* ``field_one__object__int_arr__item__index``\n* ``field_one__object__num_arr__item``\n* ``field_one__object__num_arr__item__index``\n* ``field_one__object__str_arr__item``\n* ``field_one__object__str_arr__item__index``\n\nGiven the following JSON data in one record::\n\n {\n \"object\": {\n \"int_arr\": [42, 77, 1664],\n \"num_arr\": [3.141592, 1.618034, 37.2],\n \"str_arr\": [\"pouet\", \"foo\", \"bar\"]\n }\n }\n\nThe ``result`` variable would contain this:\n\n+----------------------------------+-----------------------------------------+----------------------------------+-----------------------------------------+----------------------------------+-----------------------------------------+\n| field_one__object__int_arr__item | field_one__object__int_arr__item__index | field_one__object__num_arr__item | field_one__object__num_arr__item__index | field_one__object__str_arr__item | field_one__object__str_arr__item__index |\n+==================================+=========================================+==================================+=========================================+==================================+=========================================+\n| 42 | 1 | NULL | NULL | NULL | NULL |\n+----------------------------------+-----------------------------------------+----------------------------------+-----------------------------------------+----------------------------------+-----------------------------------------+\n| 77 | 2 | NULL | NULL | NULL | NULL |\n+----------------------------------+-----------------------------------------+----------------------------------+-----------------------------------------+----------------------------------+-----------------------------------------+\n| 1664 | 3 | NULL | NULL | NULL | NULL |\n+----------------------------------+-----------------------------------------+----------------------------------+-----------------------------------------+----------------------------------+-----------------------------------------+\n| NULL | NULL | 3.141592 | 1 | NULL | NULL |\n+----------------------------------+-----------------------------------------+----------------------------------+-----------------------------------------+----------------------------------+-----------------------------------------+\n| NULL | NULL | 1.618034 | 2 | NULL | NULL |\n+----------------------------------+-----------------------------------------+----------------------------------+-----------------------------------------+----------------------------------+-----------------------------------------+\n| NULL | NULL | 37.2 | 3 | NULL | NULL |\n+----------------------------------+-----------------------------------------+----------------------------------+-----------------------------------------+----------------------------------+-----------------------------------------+\n| NULL | NULL | NULL | NULL | pouet | 1 |\n+----------------------------------+-----------------------------------------+----------------------------------+-----------------------------------------+----------------------------------+-----------------------------------------+\n| NULL | NULL | NULL | NULL | foo | 2 |\n+----------------------------------+-----------------------------------------+----------------------------------+-----------------------------------------+----------------------------------+-----------------------------------------+\n| NULL | NULL | NULL | NULL | bar | 3 |\n+----------------------------------+-----------------------------------------+----------------------------------+-----------------------------------------+----------------------------------+-----------------------------------------+\n\n\nNested objects\n==============\n\nGiven the following data description::\n\n {\n \"type\": \"object\",\n \"properties\": {\n \"object\": {\n \"type\": \"object\",\n \"properties\": {\n \"nested\": {\n \"type\": \"object\",\n \"properties\": {\n \"obj_int\": {\"type\": \"integer\"},\n \"obj_num\": {\"type\": \"number\"},\n \"obj_str\": {\"type\": \"string\"}\n }\n }\n }\n }\n }\n }\n\n``flat_selectable`` would provide the following fields:\n\n* ``field_one__object__nested__obj_int``\n* ``field_one__object__nested__obj_num``\n* ``field_one__object__nested__obj_str``\n\nGiven the following JSON data in one record::\n\n {\n \"object\": {\n \"nested\": {\n \"obj_int\": 42,\n \"obj_num\": 3.141592,\n \"obj_str\": \"pouet\"\n }\n }\n }\n\nThe ``result`` variable would contain this:\n\n+------------------------------------+------------------------------------+------------------------------------+\n| field_one__object__nested__obj_int | field_one__object__nested__obj_num | field_one__object__nested__obj_str |\n+====================================+====================================+====================================+\n| 42 | 3.141592 | pouet |\n+------------------------------------+------------------------------------+------------------------------------+\n\n\nNested arrays\n=============\n\nGiven the following data description::\n\n {\n \"type\": \"object\",\n \"properties\": {\n \"nested_arrays\": {\n \"type\": \"array\",\n \"items\": {\n \"type\": \"array\",\n \"items\": {\"type\": \"string\"}\n }\n }\n }\n }\n\n``flat_selectable`` would provide the following fields:\n\n* ``field_one__nested_arrays__item__index``\n* ``field_one__nested_arrays__item__item__index``\n* ``field_one__nested_arrays__item__item``\n\nGiven the following JSON data in one record::\n\n {\n \"nested_arrays\": [\n [\"abc\", \"def\"],\n [\"tuw\", \"xyz\"]\n ]\n }\n\nThe ``result`` variable would contain this:\n\n+---------------------------------------+---------------------------------------------+--------------------------------------+\n| field_one__nested_arrays__item__index | field_one__nested_arrays__item__item__index | field_one__nested_arrays__item__item |\n+=======================================+=============================================+======================================+\n| 1 | 1 | abc |\n+---------------------------------------+---------------------------------------------+--------------------------------------+\n| 1 | 2 | def |\n+---------------------------------------+---------------------------------------------+--------------------------------------+\n| 2 | 1 | tuw |\n+---------------------------------------+---------------------------------------------+--------------------------------------+\n| 2 | 2 | xyz |\n+---------------------------------------+---------------------------------------------+--------------------------------------+\n\n\nData \"preservation\"\n===================\n\nthe `pg_jsonb_flattener` tool allows to specify chunks of JSON data not to be\nflattened by providing a full name of data to preserve in a list passed at\n`JsonbTable` instantiation via the `preserved` optional argument. See examples\nbelow with nested arrays:\n\n.. code-block:: python\n\n jsonb_table = JsonbTable(ONE_JSON_FIELD_TABLE, field_one=DATA_DESCRIPTION,\n preserved=['field_one__nested_arrays__item'])\n\n flat_selectable = jsonb_table.get_query()\n\n with engine.connect() as conn:\n result = conn.execute(flat_selectable).fetchall()\n\nIn this case, the `flat_selectable` would provide the following fields:\n\n* ``field_one__nested_arrays__item__index``\n* ``field_one__nested_arrays__item``\n\nThe ``result`` variable would contain this:\n\n+---------------------------------------+--------------------------------+\n| field_one__nested_arrays__item__index | field_one__nested_arrays__item |\n+=======================================+================================+\n| 1 | [\"abc\", \"def\"] |\n+---------------------------------------+--------------------------------+\n| 2 | [\"tuw\", \"xyz\"] |\n+---------------------------------------+--------------------------------+\n\n\nThis also works for the nesting array and preserving the\n`field_one__nested_arrays` would result in the following fields and result:\n\n* ``field_one__nested_arrays``\n\n+----------------------------------+\n| field_one__nested_arrays |\n+==================================+\n| [[\"abc\", \"def\"], [\"tuw\", \"xyz\"]] |\n+----------------------------------+\n\n\nObjects can also be preserved, see examples with nested objects.\n\nPreserving `field_one__object__nested` gives the following fields and result:\n\n* ``field_one__object__nested``\n\n+----------------------------------------------------------+\n| field_one__object__nested |\n+==========================================================+\n| {'obj_int': 42, 'obj_num': 3.141592, 'obj_str': 'pouet'} |\n+----------------------------------------------------------+\n\nPreserving `field_one__object` gives the following fields and result:\n\n* ``field_one__object``\n\n+----------------------------------------------------------------------+\n| field_one__object__nested |\n+======================================================================+\n| {'nested': {'obj_int': 42, 'obj_num': 3.141592, 'obj_str': 'pouet'}} |\n+----------------------------------------------------------------------+\n\n.. note::\n\n Of course, it is possible to preserve objects in arrays and arrays in objects\n\n\n", "description_content_type": "", "docs_url": null, "download_url": "", "downloads": { "last_day": -1, "last_month": -1, "last_week": -1 }, "home_page": "https://gitlab.com/askorski/pg_jsonb_flattener", "keywords": "postgresql,jsonb,sqlalchemy", "license": "MIT", "maintainer": "", "maintainer_email": "", "name": "pg-jsonb-flattener", "package_url": "https://pypi.org/project/pg-jsonb-flattener/", "platform": "", "project_url": "https://pypi.org/project/pg-jsonb-flattener/", "project_urls": { "Homepage": "https://gitlab.com/askorski/pg_jsonb_flattener" }, "release_url": "https://pypi.org/project/pg-jsonb-flattener/0.2.0/", "requires_dist": [ "psycopg2", "sqlalchemy", "flake8 ; extra == 'dev'", "flake8-html ; extra == 'dev'", "sphinx ; extra == 'dev'", "sphinx-rtd-theme ; extra == 'dev'", "coverage ; extra == 'dev'", "pytest ; extra == 'dev'", "pytest-cov ; extra == 'dev'", "pytest-html ; extra == 'dev'", "coverage ; extra == 'test'", "pytest ; extra == 'test'", "pytest-cov ; extra == 'test'", "pytest-html ; extra == 'test'" ], "requires_python": "", "summary": "JSONB data flattener for Postgresl", "version": "0.2.0" }, "last_serial": 4802706, "releases": { "0.1.0": [ { "comment_text": "", "digests": { "md5": "f695aebd8b5f4c228f93f49b82030607", "sha256": "40a067777b1daa42877515e3d485c67709c0016d6eba835b649240d1d00d4114" }, "downloads": -1, "filename": "pg_jsonb_flattener-0.1.0-py3-none-any.whl", "has_sig": false, "md5_digest": "f695aebd8b5f4c228f93f49b82030607", "packagetype": "bdist_wheel", "python_version": "py3", "requires_python": null, "size": 8871, "upload_time": "2019-02-08T20:32:33", "url": "https://files.pythonhosted.org/packages/56/7e/0b07f5c5a4c86def5cea00bee4ae8a4d3fe7fbf1f28a25b5f752e1df8ed1/pg_jsonb_flattener-0.1.0-py3-none-any.whl" }, { "comment_text": "", "digests": { "md5": "66e2c18b1b40c6d00faa26cf2c20b09f", "sha256": "95e7b28f4a6dc720f73648c0f6b0fd9310d64eb8813a42f7e1a93ad078619efa" }, "downloads": -1, "filename": "pg_jsonb_flattener-0.1.0.tar.gz", "has_sig": false, "md5_digest": "66e2c18b1b40c6d00faa26cf2c20b09f", "packagetype": "sdist", "python_version": "source", "requires_python": null, "size": 17301, "upload_time": "2019-02-08T20:32:43", "url": "https://files.pythonhosted.org/packages/3b/75/19a0054816aac90793556dacce718b2754c495900b284eac611b00959861/pg_jsonb_flattener-0.1.0.tar.gz" } ], "0.2.0": [ { "comment_text": "", "digests": { "md5": "5adba07cdca9a4a9bc5f0acd228bd4ce", "sha256": "3ee4afa475b70ae6ad152b9c7f9e6d9b7bec2ef0784fbb9c795b2df590922fb4" }, "downloads": -1, "filename": "pg_jsonb_flattener-0.2.0-py3-none-any.whl", "has_sig": false, "md5_digest": "5adba07cdca9a4a9bc5f0acd228bd4ce", "packagetype": "bdist_wheel", "python_version": "py3", "requires_python": null, "size": 9761, "upload_time": "2019-02-10T17:10:33", "url": "https://files.pythonhosted.org/packages/7d/95/b5f16ec310f86fca463a778ad051cd92a2a7e2a049848a38afa0f0b35a78/pg_jsonb_flattener-0.2.0-py3-none-any.whl" }, { "comment_text": "", "digests": { "md5": "5035b52ba22bc8523187427ff687ebff", "sha256": "eeb18907fe0b3265396e123f7b15eeeda73849f045afc035cd6c11f51a4e876f" }, "downloads": -1, "filename": "pg_jsonb_flattener-0.2.0.tar.gz", "has_sig": false, "md5_digest": "5035b52ba22bc8523187427ff687ebff", "packagetype": "sdist", "python_version": "source", "requires_python": null, "size": 22178, "upload_time": "2019-02-10T17:10:34", "url": "https://files.pythonhosted.org/packages/4e/8a/983d0d7c6444a491b37f7a3b24d4c56bb17709961fb634217989fb8fe752/pg_jsonb_flattener-0.2.0.tar.gz" } ] }, "urls": [ { "comment_text": "", "digests": { "md5": "5adba07cdca9a4a9bc5f0acd228bd4ce", "sha256": "3ee4afa475b70ae6ad152b9c7f9e6d9b7bec2ef0784fbb9c795b2df590922fb4" }, "downloads": -1, "filename": "pg_jsonb_flattener-0.2.0-py3-none-any.whl", "has_sig": false, "md5_digest": "5adba07cdca9a4a9bc5f0acd228bd4ce", "packagetype": "bdist_wheel", "python_version": "py3", "requires_python": null, "size": 9761, "upload_time": "2019-02-10T17:10:33", "url": "https://files.pythonhosted.org/packages/7d/95/b5f16ec310f86fca463a778ad051cd92a2a7e2a049848a38afa0f0b35a78/pg_jsonb_flattener-0.2.0-py3-none-any.whl" }, { "comment_text": "", "digests": { "md5": "5035b52ba22bc8523187427ff687ebff", "sha256": "eeb18907fe0b3265396e123f7b15eeeda73849f045afc035cd6c11f51a4e876f" }, "downloads": -1, "filename": "pg_jsonb_flattener-0.2.0.tar.gz", "has_sig": false, "md5_digest": "5035b52ba22bc8523187427ff687ebff", "packagetype": "sdist", "python_version": "source", "requires_python": null, "size": 22178, "upload_time": "2019-02-10T17:10:34", "url": "https://files.pythonhosted.org/packages/4e/8a/983d0d7c6444a491b37f7a3b24d4c56bb17709961fb634217989fb8fe752/pg_jsonb_flattener-0.2.0.tar.gz" } ] }