4
The issue is that JSON_VALUE returns an NVARCHAR and you sent in an INTEGER. This causes a Convert_Implicit.
If you pass in a VARCHAR or NVARCHAR param/value into the WHERE condition, it does a seek and drops the rows read.
3
Use conditional aggregation to get the sums depending on is_tag_stock and jsonb_build_object() (or json_build_object()) to create the encapsulating JSON.
SELECT cat_id,
cat_name,
jsonb_build_object('piece',
sum(CASE
WHEN is_tag_stock THEN
piece
...
1
UPDATE table1
SET jsonfield = (
SELECT jsonb_set(jsonfield
, '{parent,0,steps}'::text[] || (idx - 1)::text
, val->'childStep'->0 || (val - text 'childStep')
)
FROM jsonb_array_elements(jsonfield#>'{parent,0,steps}') WITH ORDINALITY a(val, idx)
WHERE val->>'name' = 'CONFIG2'
...
answered Dec 30 '20 at 14:51
Erwin Brandstetter
126k16 gold badges290 silver badges430 bronze badges
1
Be aware that order of array items is a different issue than order of keys in JSON object. See:
How to sort objects in an array inside a json or jsonb value by a property of the objects?
For your first example (implying integer sort), simply casting to jsonb would do the job. It happens to sort keys the way you want by default:
SELECT jsonitem::jsonb FROM ...
Only top voted, non community-wiki answers of a minimum length are eligible
