Hot answers tagged

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' ...


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