{"id":2769,"date":"2025-01-13T14:39:31","date_gmt":"2025-01-13T14:39:31","guid":{"rendered":"https:\/\/bynatree.com\/?p=2769"},"modified":"2025-01-13T14:39:31","modified_gmt":"2025-01-13T14:39:31","slug":"efficiently-storing-and-querying-json-data-in-postgresql","status":"publish","type":"post","link":"https:\/\/divaind.com\/ie1\/2025\/01\/13\/efficiently-storing-and-querying-json-data-in-postgresql\/","title":{"rendered":"Efficiently Storing and Querying JSON Data in PostgreSQL"},"content":{"rendered":"

JSON Data, short for JavaScript Object Notation, is an open standard format that structures data in key-value pairs and arrays. Widely used in web services, JSON serves as a versatile format for data exchange, document storage, and managing unstructured data.<\/span><\/p><\/blockquote>\n

PostgreSQL provides two JSON data types:<\/span><\/p>\n

    \n
  1. JSON<\/b>: Stores data in raw JSON format, where the data is stored as a plain text string and only validates basic JSON syntax upon insertion.<\/span><\/li>\n
  2. JSONB<\/b>: Stores data in a binary format after parsing and validating the JSON structure. This parsed, binary format allows for efficient indexing, faster lookups, and optimized storage, making it ideal for operations that require frequent access or querying of JSON data.<\/span><\/li>\n<\/ol>\n

     <\/p>\n

    Why JSON data type ?<\/b><\/h2>\n

     <\/p>\n

    The JSON data type in PostgreSQL is highly beneficial when dealing with data that doesn\u2019t fit neatly into a fixed schema. It is ideal in cases where:<\/span><\/p>\n

    \u00a0<\/span><\/p>\n

      \n
    • Input Structure is Unpredictable<\/b>: JSON is well-suited for capturing data with varying fields and values, as might occur with user-generated content, customer forms, or API responses.<\/span><\/li>\n<\/ul>\n
        \n
      • Sparse or Optional Fields<\/b>: When certain fields are only occasionally used, JSON allows you to store them without rigid schema constraints, saving space and reducing schema complexity.<\/span><\/li>\n<\/ul>\n
          \n
        • Mixed Data Types<\/b>: JSON can handle complex data structures, such as arrays or nested objects, making it easier to work with semi-structured data that might not align with traditional table structures.<\/span><\/li>\n<\/ul>\n
            \n
          • Rapid Development and Iteration<\/b>: JSON\u2019s schema-less format makes it ideal for projects requiring frequent changes, as you can modify fields without altering the database schema.<\/span><\/li>\n<\/ul>\n

             <\/p>\n

            How to store the Json data<\/b><\/h2>\n

             <\/p>\n

            Create the table with jsonb data type and store the data using the regular insert query. Here is an example which generate the random data of 1Million rows. I have used the function “jsonb_build_object” which create the json object from raw data.<\/span><\/p>\n

             <\/p>\n

            jsonb_build_object<\/b> is a PostgreSQL function that creates a JSONB object from a set of key-value pairs, where each key and value can be specified as separate arguments. This function is particularly useful for constructing JSONB objects dynamically in SQL queries or functions.<\/span><\/p>\n

            Syntax:<\/i><\/b> jsonb_build_object(key1, value1, key2, value2, …)<\/span><\/p>\n

            postgres=# SELECT jsonb_build_object('name', 'Alice', 'age', 30, 'city', 'New York');\n            \tjsonb_build_object\n--------------------------------------------------\n {\"age\": 30, \"city\": \"New York\", \"name\": \"Alice\"}\n(1 row)\n<\/pre>\n
            postgres=# CREATE TABLE sample_data (\npostgres(# \tid SERIAL PRIMARY KEY,\npostgres(# \tdata JSONB\npostgres(# );\nCREATE TABLE\npostgres=#\npostgres=# INSERT INTO sample_data (data)\npostgres-# SELECT jsonb_build_object(\npostgres(# \t'name', 'User ' || trunc(random() * 100)::int,\npostgres(# \t'age', trunc(random() * 50 + 20)::int,\npostgres(# \t'address', jsonb_build_object(\npostgres(#     \t'street', 'Street ' || trunc(random() * 100)::int,\npostgres(#     \t'city', CASE WHEN random() < 0.5 THEN 'City A' ELSE 'City B' END,\npostgres(#     \t'zip', trunc(random() * 90000 + 10000)::int\npostgres(# \t),\npostgres(# \t'is_active', random() < 0.5\npostgres(# )\npostgres-# FROM generate_series(1, 1000000);\nINSERT 0 1000000\npostgres=#\n<\/pre>\n
            How to query the json data<\/b><\/h2>\n
            JSONB supports various operators which are helpful to query the json data.<\/span><\/p>\n
            Existence Operators ( ?, ?&, ?|)<\/b><\/h3>\n
            Existence Operator (?) is used to verify if a particular field exists at the top place or not. Here is an example where to find the employees who has email field.<\/span><\/p>\n
            postgres=# INSERT INTO employee (data)\npostgres-# VALUES ('{\npostgres'#   \"id\": 1,\npostgres'#   \"name\": \"Venkat\",\npostgres'#   \"age\": 30,\npostgres'#   \"address\": {\npostgres'# \t\"street\": \"Stree 1\",\npostgres'# \t\"city\": \"Hyderabad\",\npostgres'# \t\"zip\": \"500049\"\npostgres'#   },\npostgres'#   \"created_at\": \"2024-11-09T12:34:56Z\"\npostgres'# }'::jsonb);\nINSERT 0 1\npostgres=#\npostgres=#\npostgres=# select * from employee where data ? 'email';\n                                                                                                data\n \n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n--------\n {\"id\": 1, \"age\": 30, \"name\": \"Anurag Shukla\", \"email\": \"anuragshukla@bynatree.com\", \"address\": {\"zip\": \"500049\", \"city\": \"Hyderabad\", \"street\": \"Street 1\"}, \"created_at\": \"2024-11-09T12:3\n4:56Z\"}\n(1 row)\n<\/pre>\n
            ?& is a variant of ? Which is used to find if all fields in the given array are present at the root level in the json string.
            \n<\/span><\/p>\n
            postgres=# select * from employee where data ?& array['email','name'];\n                                                                                                data\n \n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n--------\n {\"id\": 1, \"age\": 30, \"name\": \"Anurag Shukla\", \"email\": \"anuragshukla@bynatree.com\", \"address\": {\"zip\": \"500049\", \"city\": \"Hyderabad\", \"street\": \"Street 1\"}, \"created_at\": \"2024-11-09T12:3\n4:56Z\"}\n(1 row)\n<\/pre>\n
            This is another variant of ? Which is used to find the rows which has at least one of the field in the array at the root level in json string.<\/span><\/p>\n
            postgres=# select * from employee where data ?| array['email','name'];\n                                                                                                data\n \n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n--------\n {\"id\": 1, \"age\": 30, \"name\": \"Anurag Shukla\", \"email\": \"anuragshukla@bynatree.com\", \"address\": {\"zip\": \"500049\", \"city\": \"Hyderabad\", \"street\": \"Street 1\"}, \"created_at\": \"2024-11-09T12:3\n4:56Z\"}\n {\"id\": 2, \"age\": 27, \"name\": \"Venkat\", \"address\": {\"zip\": \"500049\", \"city\": \"Hyderabad\", \"street\": \"Stree 1\"}, \"created_at\": \"2024-11-10T12:34:56Z\"}\n(2 rows)\n\n<\/pre>\n
             <\/p>\n
            Extract Operators (-> & ->> , #> & #>>)<\/b><\/h3>\n
            -> operator will extract the fields and give the output in jsonb format.<\/span><\/p>\n
            postgres=# select data -> 'id' as ID, data -> 'name' as NAME from employee ;\n id |      name\n----+-----------------\n 1  | \"Anurag Shukla\"\n(1 row)\n \npostgres=# select pg_typeof(data -> 'id') as ID, pg_typeof(data -> 'name') as NAME from employee ;\n  id   | name\n-------+-------\n jsonb | jsonb\n(1 row)\n\n<\/pre>\n
            ->> operator will extract the data in string format<\/span><\/p>\n
            postgres=# select data ->> 'id' as ID, data ->> 'name' as NAME from employee ;\n id |     name\n----+---------------\n 1  | Anurag Shukla\n(1 row)\n \npostgres=# select pg_typeof(data ->> 'id') as ID, pg_typeof(data ->> 'name') as NAME from employee ;\n  id  | name\n------+------\n text | text\n(1 row)\n\n<\/pre>\n
            This operators (#>, #>>) are to get the data at the given path. The “#>” will get output in jsonb format and the “#>>” will get the data in text format.<\/span><\/p>\n
            postgres=# select data -> 'id' as ID , data -> 'name' as NAME , data #> '{address,city}' as CITY from employee;\n id |      name   \t|\tcity\n----+-----------------+-------------\n 1  | \"Anurag Shukla\" | \"Hyderabad\"\n 2  | \"Venkat\"    \t| \"Hyderabad\"\n(2 rows)\n \npostgres=# select data ->> 'id' as ID , data ->> 'name' as NAME , data #>> '{address,city}' as CITY from employee;\n id |     name  \t|   city\n----+---------------+-----------\n 1  | Anurag Shukla | Hyderabad\n 2  | Venkat    \t| Hyderabad\n(2 rows)\n<\/pre>\n
            Contain operators (@> , <@)<\/b><\/h3>\n
            \u00a0<\/span>This is used to verify if a json string is present in another json string. Here is an example where verifying if the {“id”: 1} is present in the data column.<\/span><\/p>\n
            postgres=# select * from employee where '{\"id\": 1}'::jsonb <@ data;\n                                                                                                data\n \n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n--------\n {\"id\": 1, \"age\": 30, \"name\": \"Anurag Shukla\", \"email\": \"anuragshukla@bynatree.com\", \"address\": {\"zip\": \"500049\", \"city\": \"Hyderabad\", \"street\": \"Street 1\"}, \"created_at\": \"2024-11-09T12:3\n4:56Z\"}\n(1 row)\n \npostgres=# select * from employee where data @> '{\"id\": 1}'::jsonb;\n                                                                                                data\n \n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n--------\n {\"id\": 1, \"age\": 30, \"name\": \"Anurag Shukla\", \"email\": \"anuragshukla@bynatree.com\", \"address\": {\"zip\": \"500049\", \"city\": \"Hyderabad\", \"street\": \"Street 1\"}, \"created_at\": \"2024-11-09T12:3\n4:56Z\"}\n(1 row)\n<\/pre>\n
            jsonb_path_query<\/b><\/h3>\n
            \u00a0<\/span><\/p>\n
            This is a new feature added in PostgreSQL 12 version and it makes PostgreSQL is more adheres to the SQL standard 2016 and provides below functions which makes the data processing ease.<\/span><\/p>\n
              \n
            • jsonb_path_exists<\/span><\/li>\n
            • jsonb_path_match<\/span><\/li>\n
            • jsonb_path_query_array<\/span><\/li>\n
            • jsonb_path_query_first<\/span><\/li>\n<\/ul>\n
              Indexing JSON columns<\/b><\/h2>\n
              B-TREE<\/b><\/h3>\n
              This can handle only equality operator<\/span><\/p>\n
              postgres=# explain select * from employee where data='{\"id\": 1, \"age\": 30, \"name\": \"Anurag Shukla\", \"email\": \"anuragshukla@bynatree.com\", \"address\": {\"zip\": \"500049\", \"city\": \"Hyderabad\", \"street\": \"Street 1\"}, \"created_at\": \"2024-11-09T12:34:56Z\"}';\n                                                                                                                QUERY PLAN\n \n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n----------------------------------------------\n Bitmap Heap Scan on employee  (cost=84.83..229.56 rows=39 width=32)\n   Recheck Cond: (data = '{\"id\": 1, \"age\": 30, \"name\": \"Anurag Shukla\", \"email\": \"anuragshukla@bynatree.com\", \"address\": {\"zip\": \"500049\", \"city\": \"Hyderabad\", \"street\": \"Street 1\"}, \"crea\nted_at\": \"2024-11-09T12:34:56Z\"}'::jsonb)\n   ->  Bitmap Index Scan on data_btree_idx  (cost=0.00..84.83 rows=39 width=0)\n     \tIndex Cond: (data = '{\"id\": 1, \"age\": 30, \"name\": \"Anurag Shukla\", \"email\": \"anuragshukla@bynatree.com\", \"address\": {\"zip\": \"500049\", \"city\": \"Hyderabad\", \"street\": \"Street 1\"}, \"\ncreated_at\": \"2024-11-09T12:34:56Z\"}'::jsonb)\n(4 rows)\n<\/pre>\n
              If you try to use other operators it will not use the index.<\/span><\/p>\n
              postgres=# explain select * from employee where data ->> 'id' = '1';\n                              \tQUERY PLAN\n------------------------------------------------------------------------------\n Gather  (cost=1000.00..5740.70 rows=655 width=205)\n   Workers Planned: 2\n   ->  Parallel Seq Scan on employee  (cost=0.00..4675.20 rows=273 width=205)\n     \tFilter: ((data ->> 'id'::text) = '1'::text)\n(4 rows)\n<\/pre>\n
              But B-Tree indexes can be created on specific fields of a json column using functional index.<\/span><\/p>\n
              postgres=# create index id_idx on employee ((data->>'id'));\nCREATE INDEX\n<\/pre>\n
              HASH Index<\/b><\/h3>\n
              HASH index can be used to index json column which is similar to B-Tree index. This also works only for equality operator.<\/span><\/p>\n
              For both B-Tree and HASH indexes like operator will not work.<\/span><\/p>\n
              GIN Index<\/b><\/h3>\n
              \u00a0<\/span>GIN index supports below operator classes and each operator class supports various operations which can be indexed. Jsonb_ops, jsonb_path_ops are the most widely used operator classes.<\/span><\/p>\n
              \"\"<\/p>\n
              Jsonb_path_ops covers the containment operator (@).<\/span><\/p>\n
               <\/p>\n
              postgres=# create index idx_jsonb_path_ops on employee using GIN (data jsonb_path_ops);\nCREATE INDEX\n \npostgres=# explain select * from employee where '{\"id\": 1}'::jsonb <@ data;\n                                  \tQUERY PLAN\n---------------------------------------------------------------------------------------\n Bitmap Heap Scan on employee  (cost=445.17..5114.91 rows=65099 width=205)\n   Recheck Cond: ('{\"id\": 1}'::jsonb <@ data)\n   ->  Bitmap Index Scan on idx_jsonb_path_ops  (cost=0.00..428.89 rows=65099 width=0)\n     \tIndex Cond: (data @> '{\"id\": 1}'::jsonb)\n(4 rows)\n\n<\/pre>\n
              Jsonb_ops supports containment (@) and existence operator (?).
              \n<\/span><\/p>\n
              postgres=# create index idx_jsonb_ops on employee using GIN (data jsonb_ops);\nCREATE INDEX\npostgres=# explain select * from employee where data ? 'email';\n                                \tQUERY PLAN\n----------------------------------------------------------------------------------\n Bitmap Heap Scan on employee  (cost=445.17..5114.91 rows=65099 width=205)\n   Recheck Cond: (data ? 'email'::text)\n   ->  Bitmap Index Scan on idx_jsonb_ops  (cost=0.00..428.90 rows=65099 width=0)\n     \tIndex Cond: (data ? 'email'::text)\n(4 rows)\n \npostgres=# explain select * from employee where data ?& array['email','name'];\n                                \tQUERY PLAN\n----------------------------------------------------------------------------------\n Bitmap Heap Scan on employee  (cost=471.26..5143.51 rows=65300 width=205)\n   Recheck Cond: (data ?& '{email,name}'::text[])\n   ->  Bitmap Index Scan on idx_jsonb_ops  (cost=0.00..454.93 rows=65300 width=0)\n     \tIndex Cond: (data ?& '{email,name}'::text[])\n(4 rows)\n<\/pre>\n
              Limitations of Json data type<\/b><\/h2>\n
              While JSON data types (particularly JSONB) in PostgreSQL offer flexibility, there are several limitations to consider:<\/span><\/p>\n
                \n
              1. Lack of Detailed Statistics<\/b>: PostgreSQL cannot capture statistics for individual fields within JSONB columns, which can lead the query planner to make suboptimal decisions. As a result, queries on less frequent fields may be slower than expected due to inaccurate cost estimates.<\/span><\/li>\n
              2. Increased Storage Usage<\/b>: JSONB doesn\u2019t deduplicate keys, so if the same key appears repeatedly across JSONB documents, each instance consumes storage. This can lead to higher storage costs, especially in large datasets with many repeated keys.<\/span><\/li>\n
              3. Indexing Overheads<\/b>: While JSONB supports indexing on specific fields, adding many indexes to JSONB fields can increase maintenance overhead. Index updates can become costly in terms of storage and performance, particularly for frequently updated data.<\/span><\/li>\n
              4. Performance Impacts on Large JSON Data<\/b>: Although JSONB is designed for better performance, querying or updating large JSON documents can be slower than equivalent operations on traditional columns due to the need to parse and process the entire document.<\/span><\/li>\n<\/ol>\n
                \n","protected":false},"excerpt":{"rendered":"
                JSON Data, short for JavaScript Object Notation, is an open standard format that structures data in key-value pairs and arrays. Widely used in web services, JSON serves as a versatile format for data exchange, document storage, and managing unstructured data. PostgreSQL provides two JSON data types: JSON: Stores data in raw JSON format, where the…<\/p>\n","protected":false},"author":1,"featured_media":2771,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[28],"tags":[66,109,115,150,180,181,182,183,184,185,273,277,279,283,287,298],"class_list":["post-2769","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-postgresql","tag-advanced-postgresql","tag-database-best-practices","tag-database-optimization","tag-efficient-json-handling","tag-json-data-types","tag-json-functions","tag-json-indexing","tag-json-querying","tag-json-storage","tag-jsonb-vs-json","tag-postgresql-database","tag-postgresql-json","tag-postgresql-performance","tag-postgresql-tips","tag-postgresql-tutorials","tag-query-performance","category-28","description-off"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts\/2769","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/comments?post=2769"}],"version-history":[{"count":0,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts\/2769\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/media\/2771"}],"wp:attachment":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/media?parent=2769"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/categories?post=2769"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/tags?post=2769"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}