Selecting Specific Keys from a JSON Object Dynamically Using Postgres Functions

Selecting Specific Keys from a JSON Object Dynamically

In this article, we’ll explore the problem of selecting specific keys from a JSON object dynamically. We’ll start with an overview of the problem and then dive into the solution.

Problem Overview

We have a Python function called get_sandbox_csv_query that generates a SQL query to select columns from a JSON object. The query uses the string_agg function to concatenate column names into a single string. We also have another query that joins multiple tables, generates output for products, and we want to display only items specified by get_sandbox_csv_query.

The problem arises when we try to extract specific keys from the JSON object. We can’t simply use json_agg because some rows don’t have certain columns.

Solution

We’ll create a custom Postgres function called f_json_select_keys that takes an array of key names and returns a JSON object with only those keys. Here’s the code:

CREATE OR REPLACE FUNCTION f_json_select_keys(_js json, _keys text[])
  RETURNS json
  LANGUAGE sql STABLE STRICT PARALLEL SAFE AS
$func$
SELECT json_object_agg (t.key, t.value ORDER BY ord)
FROM   unnest(_keys) WITH ORDINALITY k(key, ord)
JOIN   json_each(_js) t USING (key);
$func$

This function uses json_each to iterate over the JSON object and unnest to iterate over the array of key names. It then joins these two iterables together using join and finally aggregates the resulting values into a single JSON object.

We’ll also modify our get_sandbox_csv_query function to return an array of column names instead of concatenating them:

def get_sandbox_csv_query(self):
    q = """
    SELECT ARRAY (
        SELECT json_field_name
        FROM   sandbox_configurations
        WHERE  include
        ORDER  BY sequence_id, sandbox_column_config_id
    ) 
    """
    return self.engine.execute(q).fetchall()

Example Usage

We’ll use our custom function to select specific keys from the JSON object. Here’s an example:

SELECT COALESCE(
         json_agg(
            f_json_select_keys(
               to_json(a.*)
             , ARRAY (SELECT json_field_name  -- your "get_sandbox_csv_query()" inlined
                      FROM   sandbox_configurations
                      WHERE  include
                      ORDER  BY sequence_id, sandbox_column_config_id) 
            )
         )
       , '[]'::json
       )
FROM  (
      -- your subquery
      ) a;

This code will return only the specified columns from the JSON object.

Choosing the Right Variant

We chose to use the json variant preserving order of keys and duplicates. However, you might want to choose a different variant depending on your requirements:

jsonb: If you’re using PostgreSQL 10 or later, you can use the jsonb variant, which is similar to JSON but uses byte arrays instead of strings.
json: This is the most basic variant and doesn’t preserve order of keys or duplicates.
array: This is not suitable for selecting specific keys from a JSON object.

We’ll explore each variant in more detail:

How to select sub-object with given keys from JSONB?

To select a sub-object with given keys from a JSONB object, you can use the following code:

SELECT jsonb_set(jsonb_object_agg(key, value), key, value) 
FROM   jsonb_array_elements(_js);

This will return an aggregated JSONB object with only the specified keys.

How to select sub-object with given keys from JSON?

To select a sub-object with given keys from a JSON object, you can use the following code:

SELECT json_object_agg(key, value) 
FROM   json_array_elements(_js);

This will return an aggregated JSON object with only the specified keys.

How to select array of key names?

To select an array of key names from a JSON object, you can use the following code:

SELECT ARRAY (
    SELECT jsonb_array_element_text(jsonb_object_to_json(_js))
    FROM   jsonb_array_elements(_js)
);

This will return an array of text values representing the keys in the JSON object.

Choosing the Right Variant

We chose to use the json variant because it’s the most basic and widely supported. However, you might want to choose a different variant depending on your specific requirements:

Preserving order of keys: If you need to preserve the order of keys, use the json or jsonb variants.
Handling duplicates: If you need to handle duplicates, use the jsonb variant.