SELECT DISTINCT in column of type JSON in postgresql

Question

SELECT DISTINCT in column of type JSON in postgresql

Navigation

#1 by (1 votes)

0

I have a table that has 2 columns, id (int auto_increment) and resp (json).

In this table I have duplicate values in the resp column, something like this:

id | resp
--------------------------------------------
1  | {"nome": "Jonh Doe", "idade": 20} 
2  | {"nome": "Jonh Doe", "idade": 20}
3  | {"nome": "Maria Claire", "idade": 38} 
4  | {"nome": "James Connor", "idade": 50}

I would just need to make a famous and traditional SELECT DISTINCT resp FROM tabela , but because of the resp column it is of type JSON , I can not do DISTINCT, PostgreSQL returns the following message:

ERROR:  could not identify an equality operator for type json

I've already researched some solutions and tried the following, but they did not work:

Try 1:

SELECT
  DISTINCT field
FROM (
  SELECT jsonb_object_keys(resp) AS field
  FROM public.tabela
    ) AS subquery
--------------
ERROR: function jsonb_object_keys(json) does not exist

Attempt 2:

SELECT
  DISTINCT field
FROM (
  SELECT jsonb_array_elements(resp) AS field
  FROM public.tabela
    ) AS subquery
--------------
ERROR: function jsonb_array_elements(json) does not exist

Try 3:

SELECT
  DISTINCT field
FROM (
  SELECT json_array_elements(resp) AS field
  FROM public.tabela
    ) AS subquery
--------------
ERROR: could not identify an equality operator for type json

sql postgresql

asked by anonymous 20.12.2018 / 12:30

1 answer

Force a shell script to run in the terminal Oracle migration to sql server

score 1 · Accepted Answer

In postgresql you can YES group by json columns, you just need to specify the columns that should serve for grouping. In this post you can see how; your query would look like this:

select val->>'nome' as field, val->>'idade' as age, count(*) as total 
from tabelateste 
group by val->>'nome', val->>'idade'

Look here for a fiddle running.