Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

create table table_name (
  details jsonb
);

insert into table_name values
('{
	"ranges": [{
			"areaRange": {
				"unit": "SQUARE",
				"range": {
					"to": 25.399999618530273,
					"from": 24.360000610351562
				}
			}
		},
		{
			"areaRange": {
				"unit": "SQUARE",
				"range": {
					"to": 44.560001373291016,
					"from": 31.969999313354492
				}
			}
		},
		{
			"areaRange": {
				"unit": "SQUARE",
				"range": {
					"to": null,
					"from": null
				}
			}
		},
		{
			"areaRange": {
				"unit": "SQUARE",
				"range": null
			}
		},
		{
			"areaRange": null
		}
	]
}');

CREATE TABLE



INSERT 0 1


select *
FROM table_name,
    jsonb_array_elements(details -> 'ranges') as ranges ;


details
value


{"ranges": [{"areaRange": {"unit": "SQUARE", "range": {"to": 25.399999618530273, "from": 24.360000610351562}}}, {"areaRange": {"unit": "SQUARE", "range": {"to": 44.560001373291016, "from": 31.969999313354492}}}, {"areaRange": {"unit": "SQUARE", "range": {"to": null, "from": null}}}, {"areaRange": {"unit": "SQUARE", "range": null}}, {"areaRange": null}]}
{"areaRange": {"unit": "SQUARE", "range": {"to": 25.399999618530273, "from": 24.360000610351562}}}

{"ranges": [{"areaRange": {"unit": "SQUARE", "range": {"to": 25.399999618530273, "from": 24.360000610351562}}}, {"areaRange": {"unit": "SQUARE", "range": {"to": 44.560001373291016, "from": 31.969999313354492}}}, {"areaRange": {"unit": "SQUARE", "range": {"to": null, "from": null}}}, {"areaRange": {"unit": "SQUARE", "range": null}}, {"areaRange": null}]}
{"areaRange": {"unit": "SQUARE", "range": {"to": 44.560001373291016, "from": 31.969999313354492}}}

{"ranges": [{"areaRange": {"unit": "SQUARE", "range": {"to": 25.399999618530273, "from": 24.360000610351562}}}, {"areaRange": {"unit": "SQUARE", "range": {"to": 44.560001373291016, "from": 31.969999313354492}}}, {"areaRange": {"unit": "SQUARE", "range": {"to": null, "from": null}}}, {"areaRange": {"unit": "SQUARE", "range": null}}, {"areaRange": null}]}
{"areaRange": {"unit": "SQUARE", "range": {"to": null, "from": null}}}

{"ranges": [{"areaRange": {"unit": "SQUARE", "range": {"to": 25.399999618530273, "from": 24.360000610351562}}}, {"areaRange": {"unit": "SQUARE", "range": {"to": 44.560001373291016, "from": 31.969999313354492}}}, {"areaRange": {"unit": "SQUARE", "range": {"to": null, "from": null}}}, {"areaRange": {"unit": "SQUARE", "range": null}}, {"areaRange": null}]}
{"areaRange": {"unit": "SQUARE", "range": null}}

{"ranges": [{"areaRange": {"unit": "SQUARE", "range": {"to": 25.399999618530273, "from": 24.360000610351562}}}, {"areaRange": {"unit": "SQUARE", "range": {"to": 44.560001373291016, "from": 31.969999313354492}}}, {"areaRange": {"unit": "SQUARE", "range": {"to": null, "from": null}}}, {"areaRange": {"unit": "SQUARE", "range": null}}, {"areaRange": null}]}
{"areaRange": null}



SELECT 5


UPDATE table_name t
SET details = jsonb_set(t.details::jsonb, '{ranges}', s.range_updated::jsonb)
FROM (
  SELECT details,
  jsonb_agg(
     jsonb_set(ranges::jsonb, '{areaRange,range,to}', 
      (round( CAST(ranges->'areaRange'->'range'->>'to' as numeric), 2) * 100)::varchar::jsonb
    )
  ) as range_updated
  FROM table_name ,
    jsonb_array_elements(details -> 'ranges') as ranges
  GROUP BY details
) s
WHERE t.details = s.details ;

SELECT * FROM table_name

UPDATE 1




details


{"ranges": [{"areaRange": {"unit": "SQUARE", "range": {"to": 2540.00, "from": 24.360000610351562}}}, {"areaRange": {"unit": "SQUARE", "range": {"to": 4456.00, "from": 31.969999313354492}}}, null, null, null]}



SELECT 1

details	value
{"ranges": [{"areaRange": {"unit": "SQUARE", "range": {"to": 25.399999618530273, "from": 24.360000610351562}}}, {"areaRange": {"unit": "SQUARE", "range": {"to": 44.560001373291016, "from": 31.969999313354492}}}, {"areaRange": {"unit": "SQUARE", "range": {"to": null, "from": null}}}, {"areaRange": {"unit": "SQUARE", "range": null}}, {"areaRange": null}]}	{"areaRange": {"unit": "SQUARE", "range": {"to": 25.399999618530273, "from": 24.360000610351562}}}
{"ranges": [{"areaRange": {"unit": "SQUARE", "range": {"to": 25.399999618530273, "from": 24.360000610351562}}}, {"areaRange": {"unit": "SQUARE", "range": {"to": 44.560001373291016, "from": 31.969999313354492}}}, {"areaRange": {"unit": "SQUARE", "range": {"to": null, "from": null}}}, {"areaRange": {"unit": "SQUARE", "range": null}}, {"areaRange": null}]}	{"areaRange": {"unit": "SQUARE", "range": {"to": 44.560001373291016, "from": 31.969999313354492}}}
{"ranges": [{"areaRange": {"unit": "SQUARE", "range": {"to": 25.399999618530273, "from": 24.360000610351562}}}, {"areaRange": {"unit": "SQUARE", "range": {"to": 44.560001373291016, "from": 31.969999313354492}}}, {"areaRange": {"unit": "SQUARE", "range": {"to": null, "from": null}}}, {"areaRange": {"unit": "SQUARE", "range": null}}, {"areaRange": null}]}	{"areaRange": {"unit": "SQUARE", "range": {"to": null, "from": null}}}
{"ranges": [{"areaRange": {"unit": "SQUARE", "range": {"to": 25.399999618530273, "from": 24.360000610351562}}}, {"areaRange": {"unit": "SQUARE", "range": {"to": 44.560001373291016, "from": 31.969999313354492}}}, {"areaRange": {"unit": "SQUARE", "range": {"to": null, "from": null}}}, {"areaRange": {"unit": "SQUARE", "range": null}}, {"areaRange": null}]}	{"areaRange": {"unit": "SQUARE", "range": null}}
{"ranges": [{"areaRange": {"unit": "SQUARE", "range": {"to": 25.399999618530273, "from": 24.360000610351562}}}, {"areaRange": {"unit": "SQUARE", "range": {"to": 44.560001373291016, "from": 31.969999313354492}}}, {"areaRange": {"unit": "SQUARE", "range": {"to": null, "from": null}}}, {"areaRange": {"unit": "SQUARE", "range": null}}, {"areaRange": null}]}	{"areaRange": null}