Claude
b83cc9988e
Co-Authored-By: Claude Sonnet 5.5 <[email protected]> Claude-Session: https://claude.ai/code/session_017ic4b6Kg9RWSTJzyz6KP9X
29 lines
1.1 KiB
Plaintext
29 lines
1.1 KiB
Plaintext
# PostgreSQL 18.6 on x86_64-pc-linux-gnu
|
|
|
|
== DDL
|
|
id bigint
|
|
name character varying
|
|
address jsonb
|
|
|
|
== stored
|
|
Asha {"zip": "411001", "city": "Pune", "floor": 3}
|
|
Ravi {"zip": "400001", "city": "Mumbai", "floor": 12}
|
|
Meera {"zip": "411045", "city": "Pune", "floor": 1}
|
|
|
|
== HQL: where c.address.city = 'Pune'
|
|
sql> select c1_0.name from customer c1_0 where cast(c1_0.address->>'city' as varchar)='Pune' order by c1_0.id
|
|
result: [Asha, Meera]
|
|
|
|
== HQL: select and order by an int attribute inside the JSON
|
|
sql> select cast(c1_0.address->>'floor' as integer) from customer c1_0 where cast(c1_0.address->>'floor' as integer)>2 order by cast(c1_0.address->>'floor' as integer)
|
|
result: [3, 12]
|
|
|
|
== HQL: update c.address.floor = 99
|
|
sql> update customer c1_0 set address=coalesce(c1_0.address,'{}')||jsonb_build_object('floor',to_jsonb(99)) where c1_0.name='Ravi'
|
|
rows updated: 1; stored: {"zip": "400001", "city": "Mumbai", "floor": 99}
|
|
|
|
== HQL: group by c.address.city
|
|
sql> select cast(c1_0.address->>'city' as varchar),count(*) from customer c1_0 group by cast(c1_0.address->>'city' as varchar) order by cast(c1_0.address->>'city' as varchar)
|
|
row: Mumbai 1
|
|
row: Pune 2
|