>>> Along the lines of the equality operator; I have ran into issues trying
to 
>>> pivot a table/result set with a json type due what seemed to be no 
>>> equality 
>>> operator. 
>> 
>> For the curious, and also use-case considerations for development, would
>> you 
>> be able to share what it is you are doing (and how) that combines full
>> json 
>> documents with pivoting? 
>> 
>> Compound types holding source data for a pivot seems problematic since 
>> generally all the pivot components are single-valued and, for data, often 
>> numerical.

>would also like to see this. json type has completely displaced 
>crosstab in my usage. I don't typically pivot json though: I pivot the 
>raw data then transform to json.  With limited exceptions I consider 
>storing json in actual table rows to be an anti-pattern (but it should 
>still work if you do it). 

I could not figure out what I was doing last month to reproduce this.  So  I
did a small pivot poc, and it is erroring on the max function. So it is
probably not the same issue. My guess is I tried the using the GREATEST
function as a hail marry (which would not have worked) and got the following
message; ERROR:  could not identify a comparison function for type json and
then thought/hopped it was the same thing when reading the emails.

CREATE TABLE bad_table_json(id int, detail_type text, details json);
INSERT INTO bad_table_json values(1, 'a', '{"a":1}'::json);
INSERT INTO bad_table_json values(1, 'b', '{"a":1}'::json);
INSERT INTO bad_table_json values(1, 'c', '{"a":1}'::json);

SELECT id
      ,MAX(CASE WHEN detail_type = 'a' THEN details END) AS a
      ,MAX(CASE WHEN detail_type = 'b' THEN details END) AS b
      ,MAX(CASE WHEN detail_type = 'c' THEN details END) AS c
  FROM bad_table_json
 GROUP BY id



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/json-datatype-and-table-bloat-tp5776182p5776947.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Reply via email to