postgres to query count when json value is match LIKE condition

Question

I have mssql query which works to query the json data which kept in database column like below:

SELECT COUNT(*) as count FROM t1.test where lower(data) LIKE '%state':'new%'

{
  "id": "DO-Test CC1",
  "state": "NEW",
  "type": "Test type",
  "items": [
    {
      "itemReports": [],
      "id": "066c7499-a4b6-4346-ac98-6a71d0ddfc36",
      "itemId": "Augmentn New",
      "quantity": 100,
      "dispenseAsWritten": false,
      "administrationInstructions": "123"
    }
  ]
}

It does not work when i run it at pgAdmin (postgres database).

i am using Docker version 19.03.4, build 9013bf5

which syntax should be replaced?

'%state':'new%' that won't work because (a) json elements won't be single quoted, and (b) you've got 2 strings there separate by a colon which is not part of those strings, so I'd expect pg to raise an exception. You're looking for '%"state":"new"%', but of course you really should be using the json operator as in the answers below. — 404
– 404, Commented Jan 13, 2020 at 9:42

score 4 · Accepted Answer · 2020-01-14 09:39:44Z

4

You don't need to use like or anything similar. You can use ->> to access the value of a key. If you don't know if it's upper or lower case, you can apply a lower() on the result of that expression:

SELECT COUNT(*) as count 
FROM t1.test 
where lower(data ->> 'state') = 'new';

If the column is defined as jsonb (which it should be) and you have a GIN index on the data column and you are sure that the state is always upper case, the following is probably more efficient:

SELECT COUNT(*) as count 
FROM t1.test 
where data @> '{"state": "NEW"}'

Update as you don't store your JSON in a jsonb column (which you should do), you need to cast the column to jsonb in order to be able to use the functions, e.g.

where lower(data::jsonb ->> 'state') = 'new';

or

where data::jsonb @> '{"state": "NEW"}

But you should really consider to change that column to jsonb rather than varchar

edited Jan 14, 2020 at 9:39

answered Jan 13, 2020 at 9:39

user330315

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

5 Comments

user2201789 Over a year ago

i am using Docker postgres version 19.03.4, build 9013bf5

user330315 Over a year ago

There is no Postgres version 19.03, the current version is 12 - what do you get when you run select version()

user330315 Over a year ago

@user12158726: see my edit how to do this with a varchar column (yes, that's the "data type")

user2201789 Over a year ago

19.03 is returned when i try to get docker postgres version. i have no idea how to check exact postgres version by using docker command.

user330315 Over a year ago

As I wrote: select version();

sticky bit · Accepted Answer · 2020-01-13 09:38:57Z

1

You can use the ->> JSON operator to access state's value as text.

SELECT ...
       FROM ...
       WHERE data->>'state' = 'NEW';

answered Jan 13, 2020 at 9:38

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

5 Comments

user2201789 Over a year ago

SQL Error [42883]: ERROR: operator does not exist: text ->> unknown Hint: No operator matches the given name and argument types. You might need to add explicit type casts. Position: 55

sticky bit Over a year ago

@user12158726: It seems like you use the wrong datatype for the column data then. It should be json or jsonb but you seem to use text or varchar.

user2201789 Over a year ago

yup, now i understand why json or jsonb query does not works at all. my database datatype is varchar. in this case i cannot use json query.

sticky bit Over a year ago

@user12158726: Well, you could change the data type properly... Would be a waste if you lose all the possibilities to work with JSON Postgres offers.

user2201789 Over a year ago

not going to change data type as that violate the original arch design, will still seeking query for it, not using json query,

Collectives™ on Stack Overflow

postgres to query count when json value is match LIKE condition

2 Answers 2

5 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related