1

I updated a few fields like this:

UPDATE designs 
SET prices = '{ "at": 507, "ch": 751, "de": 447 }' 
WHERE prices IS NULL;

Now I want to find all those rows:

SELECT * FROM designs 
WHERE prices = '{ "at": 507, "ch": 751, "de": 447 }';

But I get this error:

ERROR:  operator does not exist: json = unknown

Variations like WHERE prices LIKE '%"at": 507, "ch": 751, "de": 447%' doesn't work neither.

The field prices is from type json and used PG version is 9.3

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Claudio Bredfeldt
  • 1,423
  • 16
  • 27

2 Answers2

3

There is jsonb in Postgres 9.4, which has an equality operator. This data type effectively ignores insignificant white space (and some other insignificant details), so your query would work as is:

SELECT *
FROM   designs 
WHERE  prices = '{ "at": 507, "ch": 751, "de": 447 }';

The same is not possible with json that preserves insignificant white space, so "equality" between two json values is hard to establish. You could compare text representations, but that's not reliable:

Using pg 9.4 once more, you could also make this work with a json column, by casting the value to jsonb on the fly:

SELECT *
FROM   designs 
WHERE  prices::jsonb = '{ "at": 507, "ch": 751, "de": 447 }'::jsonb;
Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 1
    This is great to know! I should probably convert my few `JSON` fields to `JSONB`. Were the questioner using 9.4, this would be the correct answer. – Gregory Higley Dec 22 '14 at 17:11
1

Unfortunately the operator = is not defined for JSON fields. If you really want to do this, your only option is to cast as TEXT, but I'm sure you understand the potential problems with that approach, e.g.,

SELECT * FROM designs WHERE prices::TEXT = '{ "x": 3 }';

However, it just occurred to me that a safe approach to that would be:

SELECT * FROM designs WHERE prices::TEXT = '{ "x": 3 }'::JSON::TEXT;

Nope, this doesn't work. Apparently, the JSON data type preserves the whitespace of the original JSON, so if the whitespace in the two strings is different, it won't work. (I regard this as a bug, but others might disagree.)

My answer is correct for 9.3, which the questioner is using, but if you are using 9.4+, Erwin Brandstetter's answer is the better choice.

Gregory Higley
  • 15,923
  • 9
  • 67
  • 96