Working with Histrogram from C API

[sdmcallister]

So far I would do: "SELECT histogram(i)::VARCHAR FROM integers". Then I have to parse the resulting string separately. The JSON library I use though expects a quoted key like : {"42": 42} so I need to parse it using other means. Is there a better way I'm missing? Would be nice if the histogram function also returned as regular table of two columns.

[Alex-Monahan]

Hello! Have a look at using the Unnest function instead of casting to varchar. That should create a table like you are looking for!
Unnest Docs

[sdmcallister]

Hm, would you be able to provide an example?

create table integers(i integer)
INSERT INTO integers VALUES (1),(1),(3),(20),(20),(20)
select histogram(i) FROM integers; 
select unnest(histogram(i)) from integers # expects a list but hist returns map. Not sure how to fully unpack without knowing the keys in advance.

[wangfenjin]

The problem is Map data type is not supported in C API

[taniabogatsch]

Hey, maybe I am misunderstanding something here, but you need the first column as the key and the second column as the count?

Would be nice if the histogram function also returned as regular table of two columns.

This would be possible without using the histogram function at all.

CREATE TABLE integers (i integer);
INSERT INTO integers VALUES (1), (1), (3), (20), (20), (20);
SELECT i, COUNT(i) FROM integers GROUP BY I;

┌────┬──────────┐
│ i  │ count(i) │
├────┼──────────┤
│ 1  │ 2        │
│ 3  │ 1        │
│ 20 │ 3        │
└────┴──────────┘

If you need the results ordered as in the histogram function, you can add an ORDER BY i behind the GROUP BY i.

Then, you can maybe use the JSON extension to get a JSON. I don't know how that works with the C API though.

SELECT json_group_object(k, v) FROM example;
-- {"duck":42,"goose":7}

Maybe this helps. :)

[github-actions]

This issue is stale because it has been open 90 days with no activity. Remove stale label or comment or this will be closed in 30 days.

[StLeoX]

D select unnest(histogram(i)) FROM integers; 
Error: Binder Error: UNNEST() can only be applied to lists, structs and NULL

[taniabogatsch]

Hi @StLeoX. We do not support histogram inside UNNEST, so this is the intended behavior.

[github-actions]

This issue is stale because it has been open 90 days with no activity. Remove stale label or comment or this will be closed in 30 days.

[github-actions]

This issue was closed because it has been stale for 30 days with no activity.