Clientreport and PostgreSQL’s JSONB datatype

I’m a real convert to the JSONB data type. We’re using this to store the JSON clientreports submitted by each client. Using JSON allows us to trivially add more information (eg extra reports) and then make queries against both the data in the JSONB row and data in other rows (and tables).

The following SQL code demonstrates the power – it returns a list of mac addresses (with some descriptive info) from the clientreports that we haven’t already recorded in the macaddr table.

WITH hostmac AS (SELECT hostname,
jsonb_array_elements(data->'network'->'nics')->'name',
jsonb_array_elements(data->'network'->'nics')->>'macaddr' AS macaddr,
data->'dmi'->'system'->>'system-serial-number' as sno
FROM clientreportjson
)
SELECT hostmac.hostname,macaddr,sno,i.item_id,i.description FROM item i
JOIN hostmac ON (
hostmac.sno = i.serial
)
WHERE macaddr::MACADDR NOT IN (SELECT macaddr from macaddr);

Experimentation with different JSON representations revealed that where you have data on multiple items of the same type (for example several NICs on the same machine), it is better to group these together in a list rather than in a hash. This makes for easier SQL processing of the data (eg using the jsonb_array_elements() operator).

This entry was posted in Uncategorized. Bookmark the permalink.