Get the root key with jsonb_path_query()

Question

In Postgres 13, for the below query, the jsonb values are getting filtered correctly based on "priority":

SELECT jsonb_path_query(
'{
  "100": {
    "name": "test",
    "priority": 5
  },
  "101": {
    "name": "test2",
    "priority": 10
  },
  "102": {
    "name": "test3",
    "priority": 15
  }                     
}'
, '$.* ? (@.priority > 10)')

Output:

{
  "name": "test3",
  "priority": 15
}

But I want to get the root key also. In this case I want to get the root for "test3" which is "102". To achieve this, I tried the below query:

SELECT jsonb_path_query(
'{
  "100": {
    "name": "test",
    "priority": 5
  },
  "101": {
    "name": "test2",
    "priority": 10
  },
  "102": {
    "name": "test3",
    "priority": 15
  }                     
}'
, '$ ? (@.*.priority > 10)')

But the output data is not getting filtered based on "priority".
Here is the output:

{
  "100": {
    "name": "test",
    "priority": 5
  },
  "101": {
    "name": "test2",
    "priority": 10
  },
  "102": {
    "name": "test3",
    "priority": 15
  }
}

The desired output would be:

{
    "102": {
        "name": "test3",
        "priority": 15
    }
}

How can I filter the data using an SQL/JSON path expression and the get the root key as well?

I took the liberty to clarify. Correct the question if I got it wrong. — Erwin Brandstetter, Jul 24 '22 at 23:14

Erwin Brandstetter · Accepted Answer · 2022-07-24T23:55:10.647

The task would be simple with a JSON array instead of a JSON object (which would seem like the more appropriate structure for your sample data):

SELECT jsonb_path_query(
'[
    {
        "100": {
            "name": "test",
            "priority": 5
        }
    },
    {
        "101": {
            "name": "test2",
            "priority": 10
        }
    },
    {
        "102": {
            "name": "test3",
            "priority": 15
        }
    }
]'
, '$[*] ? (@.*.priority > 10)');

While working with a JSON object, one way would be with the .keyvalue() method:

SELECT jsonb_path_query(
'{
  "100": {
    "name": "test",
    "priority": 5
  },
  "101": {
    "name": "test2",
    "priority": 10
  },
  "102": {
    "name": "test3",
    "priority": 15
  }                     
}'
, '$.keyvalue() ? (@.value.priority > 10)');

Returns:

{"id": 0, "key": "102", "value": {"name": "test3", "priority": 15}}

The manual:

object . keyvalue() → array

The object's key-value pairs, represented as an array of objects containing three fields: "key", "value", and "id"; "id" is a unique identifier of the object the key-value pair belongs to

To get your desired JSON value exactly:

SELECT jsonb_build_object(j->>'key', j->'value')
FROM  (
   SELECT jsonb_path_query(
   '{
     "100": {
       "name": "test",
       "priority": 5
     },
     "101": {
       "name": "test2",
       "priority": 10
     },
     "102": {
       "name": "test3",
       "priority": 15
     }                     
   }'
   , '$.keyvalue() ? (@.value.priority > 10)') 
   ) sub(j);

Returns:

{"102": {"name": "test3", "priority": 15}}

db<>fiddle here

Get the root key with jsonb_path_query()

1 Answers1