puzzled with json <-> array-CodePudding

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

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 )

CodePudding user response：

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.