Getting Only Few Properties from JSON String in PostgreSQL

PostgreSQL provides a robust and efficient way to handle JSON data, allowing you to manipulate and transform it using SQL queries. One common requirement when working with JSON data is to extract only specific properties or fields. In this article, we will explore how to achieve this using PostgreSQL’s built-in JSON functions.

Introduction to PostgreSQL JSON

Before diving into the solution, let’s first understand what JSON is in the context of PostgreSQL. JSON (JavaScript Object Notation) is a lightweight data interchange format that allows you to represent structured data in a human-readable format. In PostgreSQL, JSON data can be stored and queried using special data types, such as jsonb or json.

When working with JSON data in PostgreSQL, it’s essential to understand the different ways to manipulate and transform it. One common approach is to use SQL queries that leverage PostgreSQL’s built-in functions for parsing and manipulating JSON data.

Problem Statement

Given a JSON string, we want to extract only specific properties or fields from the data. The JSON string contains an array of objects, each with multiple fields. We need to write a query that selects only a subset of these fields.

For example, suppose we have the following JSON string:

{
   "employee":[
      {
         "name":"Raunak",
         "Age":30,
         "sec":"A"
      },
      {
         "name":"Manoj",
         "Age":35,
         "sec":"N"
      },
      {
         "name":"Naveen",
         "Age":38,
         "sec":"D"
      }
   ]
}

We want to extract only the name field from each object in the array.

Solution

One approach to solving this problem is to use PostgreSQL’s jsonb_array_elements function, which allows us to iterate over an array of JSON objects. We can then use the $>>> operator to access specific fields within each object.

Here’s an example query that extracts only the name field from each object in the array:

WITH json_table AS (
    SELECT '{
       "employee":[
          {
             "name":"Raunak",
             "Age":30,
             "sec":"A"
          },
          {
             "name":"Manoj",
             "Age":35,
             "sec":"N"
          },
          {
             "name":"Naveen",
             "Age":38,
             "sec":"D"
          }
       ]
    }'::jsonb as jsondata
)
SELECT jsonb_build_object('employee', jsonb_agg(t1.f1)) 
FROM (
    SELECT jsonb_build_object('name', jsonb_array_elements((jsondata->>'employee')::jsonb) ->> 'name') as f1 
    FROM json_table
) t1;

In this query, we first create a temporary table json_table that contains the JSON string. We then use a subquery to extract only the name field from each object in the array using the $>>> operator.

The outer query uses the jsonb_agg function to aggregate the extracted fields into an array, and finally, we use the jsonb_build_object function to construct a JSON object with the resulting array.

Explanation

Let’s break down the query step by step:

1. We create a temporary table json_table that contains the JSON string.
2. In the subquery, we use jsonb_array_elements to iterate over the array of objects in the JSON string.
3. Within the subquery, we use $>>> to access the specific field ('name') within each object. We need to cast the JSON string to a jsonb type and then access the 'employee' field using `$»>'.
4. The outer query uses jsonb_agg to aggregate the extracted fields into an array.
5. Finally, we use jsonb_build_object to construct a JSON object with the resulting array.

Alternative Approaches

While the above solution works well for this specific problem, there are alternative approaches you can take depending on your specific requirements:

• Using jsonb_path: PostgreSQL provides a jsonb_path data type that allows you to query JSON data using a SQL-like syntax. You can use ->> and $>>> operators to access fields in the JSON string, similar to the above solution.
• Using CTEs (Common Table Expressions): Instead of using a temporary table, you can define a CTE that contains the JSON string. This approach can be useful when working with complex queries or multiple levels of nesting.

Example Use Cases

Here are some example use cases for extracting specific fields from JSON data in PostgreSQL:

• Extracting company information: Suppose you have a JSON string containing employee information, including company details. You want to extract only the company name and address.

WITH json_table AS ( SELECT ‘{ “employee”:[ { “name”:“John Doe”, “company”:{ “name”:“ABC Inc.”,“address”:“123 Main St.” } }, { “name”:“Jane Smith”, “company”:{ “name”:“XYZ Corp.”,“address”:“456 Elm St.” } } ] }’::jsonb as jsondata ) SELECT jsonb_build_object(’employee’, jsonb_agg(jsonb_build_object(’name’, t1.f1->>’name’), jsonb_build_object(‘company’, t1.f2->"name"))) FROM ( SELECT jsonb_build_object(’name’, jsonb_array_elements((jsondata->>’employee’)::jsonb) ->>’name’) as f1, jsonb_build_object(‘company’, jsonb_array_elements((jsondata->>’employee’)::jsonb) ->>‘company’::text) as f2 FROM json_table ) t1;

*   **Extracting specific fields from a nested object:** Suppose you have a JSON string containing data with multiple levels of nesting. You want to extract only the top-level field and all fields within a nested object.
    ```markdown
WITH json_table AS (
    SELECT '{
       "data":[
          {
             "id":1,
             "name":"John Doe",
             "address":{
                "street":"123 Main St.",
                "city":"New York"
             }
          },
          {
             "id":2,
             "name":"Jane Smith",
             "address":{
                "street":"456 Elm St.",
                "city":"Chicago"
             }
          }
       ]
    }'::jsonb as jsondata
)
SELECT jsonb_build_object('top_level', t1.f1, 
                         jsonb_build_object('nested_field', jsonb_agg(jsonb_build_object('key', jsonb_array_elements((t1.f2->"address")::jsonb) ->>'value')))) 
FROM (
    SELECT jsonb_build_object('id', t1.f1->>'id'), 
           jsonb_build_object('name', t1.f2->>'name'),
           jsonb_build_object('nested_field', t1.f3->"street") as f3 
    FROM (
        SELECT jsonb_build_object('id', jsonb_array_elements((jsondata->>'data')::jsonb) ->>'id'), 
               jsonb_build_object('name', jsonb_array_elements((jsondata->>'data')::jsonb) ->>'name'),
               jsonb_build_object('nested_field', jsonb_array_elements((jsondata->"data")::jsonb) ->>'street' ) as f2 
        FROM json_table
    ) t1
) t2;

These examples demonstrate various scenarios where you can extract specific fields from JSON data in PostgreSQL. By using the techniques discussed above, you can efficiently and effectively work with JSON data in your database.

Last modified on 2023-06-12