Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

puzzled with json <-> array

inspired by anonther question on SO (task is to change the value for priceRange in a json field) i have following setup:

create table house
(
    sale json
);

insert into house (sale) values ('{"houses":[{"houseId":"house100","houseLocation": "malvern","attribute":{"colour":["white","grey"],"openForInspection":{"fromTime": "0001","toTime": "2359"}},"priceRange":null}]}')

going to the element:

 select
    sale,
    sale->'houses',
    pg_typeof(sale->'houses')
    from house

results in typeof: json

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

so next try:

select
sale,
sale->'houses',
pg_typeof(sale->'houses'),
json_object_keys(sale->'houses')
from house

Query Error: error: cannot call json_object_keys on an array (the query before pg_typeof tells, its json)??

so next try:

select
sale,
sale->'houses',
pg_typeof(sale->'houses'),
(sale->'houses')[0]
from house

Query Error: error: cannot subscript type json because it is not an array ?? ( the error before it tells, its an array )

where is my mistake in type determination ?

playground:https://www.db-fiddle.com/f/k9vB34QJEKCPm9jC2He9Ev/0 ( tested with v10 and v13 )

>Solution :

To get the first element of a json array, use

sale -> 'houses' -> 0

Before PostgreSQL v14, you couldn’t use subscripts with JSON at all, and even with v14, it does not work on the data type json, only on jsonb.

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading