JSONB fields and string keys

Question

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

Kaushik Nayak · Accepted Answer · 2019-01-13 15:10:54Z

2

-> 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'

answered Jan 13, 2019 at 15:10

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

1 Answer 1