Search Postgresql Archives

Re: json aggregation question

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi Chris,

Maybe there is an another better solution;

1. sending values into jsonb_array_elements to getting elements (lateral join)
2. distinct to eliminate duplicates
3. regexp_replace to remove malformed Array literals
4. Casting into text array

SELECT 
 count(distinct tags ),
string_to_array(regexp_replace(string_agg(distinct elem::text , ','),'\[*\"*\s*\]*','','g'),',') AS list
from thing as t, jsonb_array_elements(t.tags->'tag1') elem
where tags->'tag2'?'t2val1'

count | tag1
2        | {val1,val2,val3}



28 Şub 2017 Sal, 19:22 tarihinde, Chris Withers <chris@xxxxxxxxxxxxxxxx> şunu yazdı:
Hi All,

Given the following table:

# create table thing (id serial, tags jsonb);
# \d thing
                         Table "public.thing"
 Column |  Type   |                     Modifiers                      
--------+---------+----------------------------------------------------
 id     | integer | not null default nextval('thing_id_seq'::regclass)
 tags   | jsonb   | 

...and the following data:

insert into thing (tags) values ('{"tag1": ["val1", "val2"], "tag2": ["t2val1"]}');
insert into thing (tags) values ('{"tag1": ["val3", "val1"], "tag2": ["t2val1"]}');
insert into thing (tags) values ('{"tag1": ["val2", "val1"], "tag2": ["t2val2"]}');

How can I aggregate the results of a query that equates to "show me the number of matching rows and the set of tag1 value that have a tag2 value of t2val1?

The closes I can get is:

# select count(*), json_agg(tags) from thing where tags->'tag2'?'t2val1';
 count |                                             json_agg                                             
-------+--------------------------------------------------------------------------------------------------
     2 | [{"tag1": ["val1", "val2"], "tag2": ["t2val1"]}, {"tag1": ["val3", "val1"], "tag2": ["t2val1"]}]
(1 row)

...but I really want:

 count |     tag1                                             
-------+-------------------------
     2 | ["val1", "val2", "val3"]
(1 row)
cheers,

Chris

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux