Home / Questions / 86804
Accepted
bypabloc_
Asked: 2020-07-14 10:35:27 +0800 CST 2020-07-14 10:35:27 +0800 CST

How do I query a table from a "json" type column for the rows that contain only one piece of data in that column?

  • 772

I have a table as follows:

CREATE TABLE partes(
  id INTEGER PRIMARY KEY,
  id_padre INTEGER,
  tipos JSON
);

I insert the sample data:

INSERT INTO partes (id, id_padre, tipos) VALUES (1, 0, '["1","2"]'), (2, 1, '["2"]'), (3, 1, '["2","1"]'), (4, 3, '["1"]');

I make the query:

select * from partes where tipos = '["1"]' order by id_padre;

The value I get is the following:

Query Error: error: operator does not exist: json = unknown

Here in fiddler to see how it works: dbfiddler

sql

1 Answers

  1. Best Answer

    You have two options:

    1. Change to JSONB:

      CREATE TABLE partes(
    id INTEGER PRIMARY KEY,
    id_padre INTEGER,
    tipos JSONB
);

INSERT INTO partes (id, id_padre, tipos) 
VALUES (1, 0, '["1","2"]'), 
       (2, 1, '["2"]'), 
       (3, 1, '["2","1"]'), 
       (4, 3, '["1"]');

SELECT * FROM partes WHERE tipos = '["1"]' ORDER BY id_padre;

    2. The other option is passing jsonbto your query:

      SELECT * FROM partes WHERE tipos::jsonb = '["1"]'::jsonb ORDER BY id_padre;
    • 2