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
126k1616 gold badges290290 silver badges430430 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 ...
answered Dec 27 '20 at 5:13
Erwin Brandstetter
126k1616 gold badges290290 silver badges430430 bronze badges
Only top voted, non community-wiki answers of a minimum length are eligible
Related Tags
json × 491postgresql × 319
mysql × 61
sql-server × 42
array × 42
index × 39
postgresql-9.4 × 28
sql-server-2016 × 24
performance × 18
postgresql-9.3 × 18
database-design × 17
mariadb × 16
update × 14
query × 14
postgresql-9.5 × 14
t-sql × 13
mysql-5.7 × 13
postgresql-9.6 × 13
aggregate × 12
postgresql-10 × 12
join × 11
mongodb × 10
postgresql-performance × 10
postgresql-11 × 10
plpgsql × 9