How can I assign pre-determined codes (1,2,3, etc,) to a JSON-type column in PostgreSQL?

Question

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

1. Park 1 - {covered, handicap_access, elevator} to be {1,3,7}
2. 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!

Answer 1

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.

---

step-by-step demo:db<>fiddle

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

1. Extract the array elements into one record per element. Add the WITH ORDINALITY to save the original order.
2. Join your codes on the elements
3. Create code arrays. To ensure the correct order, you can use the index values created by the WITH ORDINALITY clause.