How do I consult in a table from a column "json" the rows that contain only one data in that column?

Question

How do I consult in a table from a column "json" the rows that contain only one data in that column?

Navigation

#1 by (2 votes)

3

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 at fiddler to see how it works: dbfiddler

sql postgresql db postgresql-9.3

asked by Pablo Contreras 13.07.2017 в 20:35

source

1 answer

Is it possible to change the background color of a "Floating" Activity? Ignore multiple conditions in PHP

score 2 · Accepted Answer

You have two options:

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;

The other option is passing jsonb to your query:

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