JSON_CONTAINS() in postgresql

Question

Using MySQL 5.7, I want to know the PostgreSQL equivalent of

SELECT * FROM user_conversations WHERE JSON_CONTAINS(users, JSON_ARRAY(1))

How do you write JSON_CONTAINS(users, JSON_ARRAY(1)) in PostgreSQL

EDIT 1

there is my json it's just an array without son object :

[
 "john", 
 "doe", 
 "does"
]

i want to get "doe" for exemple

EDIT 2

My table :

  Column   |              Type              |                     Modifiers                     | Storage | Statistics Target | Description 
 ------------+--------------------------------+-------------------------------------------------------+----------+-----------------------+-------------
  id         | uuid                           | non NULL                                                   | plain    |                       | 
  name       | character varying(255)         | non NULL                                              | extended |                       | 
  image      | character varying(255)         | non NULL Par défaut, 'default.png'::character varying | extended |                       | 
  users      | json                          | non NULL                                              | extended |                       | 
  type       | integer                        | non NULL                                              | plain    |                       | 
  emoji_id   | integer                        | non NULL Par défaut, 0                                | plain    |                       | 
  created_at | timestamp(0) without time zone |                                                       | plain    |                       | 
  updated_at | timestamp(0) without time zone |

EDIT 3: I use laravel to execute queries :

DB::table('user_conversations')->whereRaw('JSON_CONTAINS(users, JSON_ARRAY(1))')->orderBy('created_at', 'desc')->paginate(5);

and it's work with mysql.

have you looked at the [library](https://www.postgresql.org/docs/9.4/static/functions-json.html). btw i have never used the mysql version nor this pgsql one. just thought that may be a good place to start — happymacarts, Dec 20 '16 at 17:33
[This question](http://stackoverflow.com/questions/19925641/check-if-a-postgres-json-array-contains-a-string) may be your ticket as well — happymacarts, Dec 20 '16 at 17:36

Evan Carroll · Answer 1 · 2016-12-20T18:38:27.850

3

The two argument form of MySQL's JSON_CONTAINS() you cite has a signature of JSON_CONTAINS(json_doc, val) That sounds similar to the PostgreSQL JSON operator @> operator

-- returns true
SELECT '["john","bob","doe","dylan","mike","does"]'::jsonb @>
  '["john","doe","does"]';

-- returns false
SELECT '["john","bob","doe","dylan","mike","does"]'::jsonb @>
  '["john","mary"]';

If your type is json, that's fine just cast it to jsonb;

SELECT *
FROM user_conversations
WHERE users::jsonb @> json_text;

edited Dec 20 '16 at 18:38

answered Dec 20 '16 at 18:20

Evan Carroll

78,363
46
261
468

i use json type not jsonb – yecir Dec 20 '16 at 18:28
@yecir you shouldn't, but it works the same, updated the answer. – Evan Carroll Dec 20 '16 at 18:34
but i want : select * from table where (json column) – yecir Dec 20 '16 at 18:37
I'm not sure, it's not clear what you're asking. JSON is a text column, validated. JSONB is a binary type that you can operate on. Text has to become binary to operate on it. – Evan Carroll Dec 20 '16 at 18:40
SELECT * FROM user_conversations WHERE users::jsonb @> "4411167c-4af8-39e8-b49a-85de76dc38cb"; ERROR: column « 4411167c-4af8-39e8-b49a-85de76dc38cb » doesn't exists – yecir Dec 20 '16 at 18:46
^ You need to use a single quote not double quotes – AlienWebguy Sep 08 '17 at 20:31
I love this! But can anyone help me with one problem? What if the search text isn't part of an array? `-- returns false because no array markers!` `SELECT '["john","bob"]'::jsonb @>` `' "john" '::jsonb; -- NOTE: array markers [ and ] were removed.` – Under-qualified NASA Intern Mar 02 '20 at 02:51

JSON_CONTAINS() in postgresql

1 Answers1