CODEWITHSUNDEEP
postgresqljsonb
Eden
Eden

Reputation: 4196

JSONB fields and string keys

I've a JSONB column with the following content:

{"ibd": true, "participant_id": "P016", "sample_participant_id": "B1"}

If I query the value without the double quotation mark I get an error:

ERROR: invalid input syntax for type json LINE 1: ...ERE sample_metadata.metadata->'sample_participant_id' = 'B1'

for:

WHERE sample_metadata.metadata->'sample_participant_id' = 'B1'

But for 

WHERE sample_metadata.metadata->'sample_participant_id' = '"B1"'

It works as expected. What I don't understand is why I need the double quotation mark. are they an actual value?

Thanks, Eden

Upvotes: 0

Views: 126

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31666

-> operator on a jsonb returns jsonb, so you can't compare a json with a string literal('B1') or text type.

WHERE sample_metadata.metadata->'sample_participant_id' = '"B1"'

works because element "B1" can be implicitly cast to jsonb for comparison

You should rather be using the ->> operator for your comparison. It returns text

WHERE metadata->>'sample_participant_id' = 'B1'

Demo

Upvotes: 2

Related Questions