Can't access value in JSON row Postgresql?

Question

Problem

having trouble trying to get values from a json object in an sql query. Im using postgresql.

Code

I'm saving the data as a json type

Schema.prisma

model Example {
  ...
  raw  Json?
 ...
}

The query for the row correctly return json

SELECT
   ex.raw
FROM 
   "Example" ex

{"type":"Eg","device":"Eg","value":1,"timestamp":"2024-02-23T05:51:19Z"}

But then trying to access the data return rows of null

SELECT
   ex.raw->>'type'
FROM 
   "Example" ex

Have tried

Casting to json & jsonb

SELECT
   ex.raw::json->>'type'
FROM 
   "Example" ex

Any help is appreciated

Answer 1

My bet is that the DB client you're inspecting this in is showing you only a few records, none of which have that key, which is why you get the nulls. Here's how I would reproduce it: demo

create table "Example" (raw json);
insert into "Example" select null from generate_series(1,2e3);
insert into "Example" values
('{"type":"Eg","device":"Eg","value":1,"timestamp":"2024-02-23T05:51:19Z"}')
returning jsonb_pretty(raw::jsonb);

jsonb_pretty
{     "type": "Eg",     "value": 1,     "device": "Eg",     "timestamp": "2024-02-23T05:51:19Z" }

And here's something PGAdmin, DataGrip, DBeaver could be doing: adding a LIMIT clause at the end (or just displaying a long list, and you just need to scroll down). Since I populated the table with 2000 null entries first, I won't necessarily see the value I entered after those.

SELECT
   ex.raw->>'type'
FROM 
   "Example" ex
LIMIT 3;

?column?
null
null
null

If you ask to order nulls last, you'll get the non-empties first.

SELECT
   ex.raw->>'type'
FROM 
   "Example" ex
ORDER BY 1 NULLS LAST
LIMIT 3;

?column?
Eg
null
null