I’m extracting a table of 2000+ rows which are park details. One of the columns is JSON type. Image of the table
We have about 15 attributes like this and we also have a documentation of pre-determined codes assigned to each attribute.
Each row in the extracted table has a different set of attributes that you can see in the image. Right now, I have cast(parks.services AS text) AS "details"
to get all the attributes for a particular park or extract just one of them using the code below:
CASE WHEN cast(parks.services AS text) LIKE '%uncovered%' THEN '2' WHEN cast(parks.services AS text) LIKE '%{covered%' THEN '1' END AS "details"
This time around, I need to extract these attributes by assigning them the codes. As an example, let’s just say
- Park 1 – {covered, handicap_access, elevator} to be {1,3,7}
- Park 2 – {uncovered, always_open, handicap_access} to be {2,5,3}
I have thought of using subquery to pre-assign the codes, but I cannot wrap my head around JSON operators – in fact, I don’t know how to extract them on 2000+ rows.
It would be helpful if someone could guide me in this topic. Thanks a lot!
Advertisement
Answer
You should really think about normalizing your tables. Don’t store arrays. You should add a mapping table to map the parks and the attribute codes. This makes everything much easier and more performant.
SELECT t.name, array_agg(c.code ORDER BY elems.index) as codes -- 3 FROM mytable t, unnest(attributes) WITH ORDINALITY as elems(value, index) -- 1 JOIN codes c ON c.name = elems.value -- 2 GROUP BY t.name
- Extract the array elements into one record per element. Add the
WITH ORDINALITY
to save the original order. - Join your codes on the elements
- Create code arrays. To ensure the correct order, you can use the
index
values created by theWITH ORDINALITY
clause.