java - Get specific jsonb element by key using a SQL query - Stack Overflow

admin管理员组

文章数量:1123401

I am storing JSON data as jsonb column and searching for specific object by key. Example:

id	data
0	{}
1	[]
2	"scalar_jsonb_value"
3	null
4	[{"name": "Keyboard", "price": 50, "currency": "USD", "item_tags": ["black", "optional"], "discount_price": 40}, {"name": "Mouse", "price": 40, "currency": "USD", "item_tags": ["white", "optional"], "discount_price": 30}]
5	[{"name": "Mouse", "price": 50, "currency": "GBP", "item_tags": ["black"], "discount_price": 50}, , {}]
6	[{"name": "Mouse", "price": 60, "currency": "CAD", "item_tags": ["red"], "discount_price": 60}, , {"name": "Mouse", "price": 61, "currency": "AUD", "item_tags": ["blue"], "discount_price": 61}]
7	[{"name": "Mouse", "price": 70, "currency": "AUD", "item_tags": ["violet"], "extra_gifts": [{"name": "Mouse", "price": 701, "currency": "DKK", "item_tags": ["purple"], "discount_price": 701}], "discount_price": 70}]
8	[{}, , {"l1": {"l2": {"name": "Mouse", "comment": "sub-sub-object of the object in the array"}}}]

I am storing JSON data as jsonb column and searching for specific object by key. Example:

id	data
0	{}
1	[]
2	"scalar_jsonb_value"
3	null
4	[{"name": "Keyboard", "price": 50, "currency": "USD", "item_tags": ["black", "optional"], "discount_price": 40}, {"name": "Mouse", "price": 40, "currency": "USD", "item_tags": ["white", "optional"], "discount_price": 30}]
5	[{"name": "Mouse", "price": 50, "currency": "GBP", "item_tags": ["black"], "discount_price": 50}, , {}]
6	[{"name": "Mouse", "price": 60, "currency": "CAD", "item_tags": ["red"], "discount_price": 60}, , {"name": "Mouse", "price": 61, "currency": "AUD", "item_tags": ["blue"], "discount_price": 61}]
7	[{"name": "Mouse", "price": 70, "currency": "AUD", "item_tags": ["violet"], "extra_gifts": [{"name": "Mouse", "price": 701, "currency": "DKK", "item_tags": ["purple"], "discount_price": 701}], "discount_price": 70}]
8	[{}, , {"l1": {"l2": {"name": "Mouse", "comment": "sub-sub-object of the object in the array"}}}]

My current SQL query:

select r.data->1
from data_set as r

gives me the correct result but I would like to get something like

select r.data->@name='Mouse' 
from data_set

Where name would be dynamic, irrespective of position of the object inside array.

• java
• sql
• json
• postgresql
• jsonb

Share Improve this question Follow edited 10 hours ago Zegarek 24.7k55 gold badges2323 silver badges2929 bronze badges asked 15 hours ago Raushan SetthiRaushan Setthi 7777 bronze badges

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 0

To achieve this in PostgreSQL with a dynamic search for an object within the JSON array based on the key name, you can use the jsonb_array_elements function, which will flatten the JSON array into individual objects. Then you can filter based on the name key within each object.

You can try this by using the following SQL query:

SELECT r.data
FROM data_set r,
LATERAL jsonb_array_elements(r.data) AS item
WHERE item->>'name' = 'Mouse';

Explanation:

• jsonb_array_elements(r.data) flattens the JSON array into individual JSON objects.
• LATERAL allows you to use each element of the array as a row in the result set.
• The ->> operator extracts the value of the name key as text from each JSON object.
• The WHERE clause filters the results to only include objects where the name is 'Mouse'.
• This approach allows you to dynamically search for objects based on the value of the name key, regardless of the position of the object inside the array.

_{demo at db<>fiddle}

select id
     , jsonb_path_query(r.data,'strict $.**?(@.name=="Mouse")')-'extra_gifts'
from data_set as r
where r.data @? '$?(@.name=="Mouse")';

id
4	{"name": "Mouse", "price": 40, "currency": "USD", "item_tags": ["white", "optional"], "discount_price": 30}
5	{"name": "Mouse", "price": 50, "currency": "GBP", "item_tags": ["black"], "discount_price": 50}
6	{"name": "Mouse", "price": 60, "currency": "CAD", "item_tags": ["red"], "discount_price": 60}
6	{"name": "Mouse", "price": 61, "currency": "AUD", "item_tags": ["blue"], "discount_price": 61}
7	{"name": "Mouse", "price": 70, "currency": "AUD", "item_tags": ["violet"], "discount_price": 70}
7	{"name": "Mouse", "price": 701, "currency": "DKK", "item_tags": ["purple"], "discount_price": 701}
8	{"name": "Mouse", "comment": "sub-sub-object of the object in the array"}

The @? JSONPath operator is a quick and flexible way to target your jsonb values based on their contents and it can speed up even more (performance demo here) using a GIN index. Containment @> and predicate @@ checks can do the same, just as quickly:

where r.data @> '[{"name":"Mouse"}]';

where r.data @@ 'exists($?(@.name=="Mouse"))';

The jsonb_path_query() unpacks each mouse into a separate row:

• strict mode removes duplicates that come with the default lax mode
• $ is the root of the jsonb value
• $.** considers everything under the root, each array element, also recursively descending into their sub-arrays and sub-objects
• ?(@.name="Mouse") is a filter expression, similar to a SQL-level WHERE clause. It'll only consider those objects with a name key present and set to "Mouse".
• -'extra_gifts' is just an example showing how to deal with nested objects. If there's a mouse that comes with a keyboard, this won't list the keyboard, only the mouse. If there's a mouse that comes with another mouse, it lists them separately, without repeating the gift that was extracted to the separate row.

The @? and @@ have the advantage of making no assumptions about the structure of the jsonb value. Your rows can hold JSONs that at their top level are objects, arrays, scalars or just null and it'll tolerate that. That's in contrast to jsonb_array_elements that strictly requires an array/null at the top level, otherwise it'll throw an exception.

The .name accessor in the jsonpath expression used with @? or @@ above also doesn't mind searching the structure deeper than one level into the element, and it actually doesn't mind how deep it has to go.

本文标签： javaGet specific jsonb element by key using a SQL queryStack Overflow