Postgres error: column does not exist when using json field

Question

Postgres version 11.10

Here is my postgres schema which uses a jsonb field

create table data(
    key       char(32), --md5 
    time      int  NOT NULL,
    output    jsonb NOT NULL,
    PRIMARY KEY (key,time)
)

Here are two queries that works just fine

select distinct on(key) key   from data ORDER BY  key,time DESC
select distinct on(key) key,output   from data ORDER BY  key,time DESC

the problem is this query

select distinct on(key) key,output-->details   from data ORDER BY  key,time DESC

I keep getting the error

column "key" does not exist

Any idea why?

@a_horse_with_no_name, thanks for the tip, i'll use text from now on — yigal
– yigal, Commented Jun 19, 2021 at 21:56

Laurenz Albe · Accepted Answer · 2021-06-19 19:34:57Z

2

-- starts a comment that lasts until the end of the line (see the syntax highlighting in your question). So the query effectively is

select distinct on(key) key,output

Since that has no FROM clause, the column reference key causes an error.

You probably are looking for the ->> operator:

select distinct on(key)
       key,
       output ->> 'details'
from data
ORDER BY  key,time DESC;

answered Jun 19, 2021 at 19:34

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

brilliant. thank you, that solved the problem