Postgres Pro
Downloads
Home   >   mailing lists

Thread: ERROR: operator does not exist: json = json

ERROR: operator does not exist: json = json

From
Erik Rijkers
Date:
Hi,

Comparison of 2 values of type jsonb is allowed.

Comparison of 2 values of type json gives an error.

That seems like an oversight -- or is it deliberate?

Example:

select '42'::json = '{}'::json;
--> ERROR:  operator does not exist: json = json

(of course, easily 'solved' by casting but that's not really the point)


Thanks,

Erik Rijkers

Re: ERROR: operator does not exist: json = json

From
Dagfinn Ilmari Mannsåker
Date:
Erik Rijkers <er@xs4all.nl> writes:

> Hi,
>
> Comparison of 2 values of type jsonb is allowed.
>
> Comparison of 2 values of type json gives an error.
>
> That seems like an oversight -- or is it deliberate?

This is because json is just a textual representation, and different
JSON strings can be semantically equal because e.g. whitespace and
object key order is not significant.

> Example:
>
> select '42'::json = '{}'::json;
> --> ERROR:  operator does not exist: json = json
>
> (of course, easily 'solved' by casting but that's not really the
> point)

To do a proper comparison you have to parse it into a semantic form,
which is what casting to jsonb does.

> Thanks,
>
> Erik Rijkers

- ilmari

Re: ERROR: operator does not exist: json = json

From
Andrew Dunstan
Date:
On 2022-07-08 Fr 07:57, Dagfinn Ilmari Mannsåker wrote:
> Erik Rijkers <er@xs4all.nl> writes:
>
>> Hi,
>>
>> Comparison of 2 values of type jsonb is allowed.
>>
>> Comparison of 2 values of type json gives an error.
>>
>> That seems like an oversight -- or is it deliberate?
> This is because json is just a textual representation, and different
> JSON strings can be semantically equal because e.g. whitespace and
> object key order is not significant.
>
>> Example:
>>
>> select '42'::json = '{}'::json;
>> --> ERROR:  operator does not exist: json = json
>>
>> (of course, easily 'solved' by casting but that's not really the
>> point)
> To do a proper comparison you have to parse it into a semantic form,
> which is what casting to jsonb does.


Alternatively, if you really need something like this, try
<https://bitbucket.org/adunstan/jsoncmp/src/master/>


(I should probably update it to mark the functions as parallel safe)


cheers


andrew


--
Andrew Dunstan
EDB: https://www.enterprisedb.com