Postgres, hstore, and JSON

Исходные данные:

• Имеем поле типа hstore в базе postgres.
• В одном из элементов hstore-поля лежит JSON вида 
• {"0":"533f4ef8.jpeg","1":"21fe5778.jpeg", ... ,"7":"dbfa227a.jpeg"}

Требуется:

• получить список всех имен фото в одну колонку.

Решение задачи:


1. Получим список всех JSON в одну колонку:
SELECT hstore_field->'photos' 
FROM "user" 
WHERE hstore_field->'photos' != '' AND hstore_field->'photos' != '{}'

2. Приведем JSON в каждой строке к стандартному массиву text[]

• Для этого напишем регулярное выражение которое убирает из JSON-строки все лишнее:

"[\d]":"([\w\\.]+)" ( нужно заменить все кроме группы 1 на '' )

• Теперь используем эту регулярку в синтаксисе postgres

REGEXP_REPLACE (hstore_field->'photos'::text, '"[\\d]":"([\\w\/\.]+)"', '"\\1"', 'g' )::text[] AS arrays

• Получим список нормальных массивов в одну колонку:

SELECT hstore_field->'photos',
REGEXP_REPLACE (hstore_field->'photos'::text, '"[\\d]":"([\\w\/\.]+)"', '"\\1"', 'g' )::text[] AS arrays
FROM "user" 
WHERE hstore_field->'photos' != '' AND hstore_field->'photos' != '{}'

3. Теперь все что осталось это вывести все элементы массивов в одну колонку, исключив повторения на всякий случай:


WITH getAllArrays AS (

    SELECT hstore_field->'photos',
        REGEXP_REPLACE (hstore_field->'photos'::text, '"[\\d]":"([\\w\/\.]+)"', '"\\1"', 'g' )::text[] AS arrays
    FROM "user" 
    WHERE hstore_field->'photos' != '' AND hstore_field->'photos' != '{}'

)
SELECT arrays[1] FROM getAllArrays
    UNION
SELECT arrays[2] FROM getAllArrays
...
    UNION
SELECT arrays[8] FROM getAllArrays

По условию задачи у меня могло быть максимум 8 элементов массива, поэтому UNION'ы я не стал оптимизировать. Если вам придет в голову что-то по этому поводу - я с удовольствием послушаю.