[Nav-users] Re: How to filter reports on Attributes

-- Ingeborg Østrem Hellemo -- ingeborg.hellemo@uit.no Dep. of Information Technology --- Univ. of Tromsø -----Original Message----- From: Morten Brekkevold morten.brekkevold@sikt.no Sent: fredag 3. februar 2023 17:26 To: Ingeborg Østrem Hellemo ingeborg.hellemo@uit.no Cc: nav-users@lister.sikt.no Subject: Re: [Nav-users] How to filter reports on Attributes On Fri, 03 Feb 2023 11:19:59 +0100 Ingeborg Hellemo ingeborg.hellemo@uit.no wrote: > morten.brekkevold@sikt.no said: >> NAV's report system comes with many built-in reports. I cannot find >> any that mention "attributes". Could you please provide more details? > > Sorry, I was sure I mentioned it: /report/room The Attributes column is a representation of the JSON blob stored in the room.data field. Unfortunately, I don't think the report tools is equipped to produce queries that can match the contents of JSON data stored in PostgreSQL. I would consider a different approach. Assuming you store structured data in the custom attributes, you can change your room report to extract those as separate columns, which you can then filter as normal columns. The room report selects the column `data`, but let's say you have a custom attribute named 'contact' that you add to rooms, you can make a column out of this by adding this to the select statement: > data->'contact' as contact I.e. you get something that begins like this: > $description = "Information about rooms."; $sql=" > SELECT > roomid, > count AS nb, > locationid, > descr, > position, > position[0] as lat, > position[1] as lon, > data->'contact' as contact > FROM room -- Sincerely, Morten Brekkevold Sikt – Norwegian Agency for Shared Services in Education and Research