Регулярное выражение «Снежинка»

У меня есть эта строка в столбце Snowflake:

\[
{
"entryListId": 3279,
"id": 4617,
"name": "SpecTra",
"type": 0
},
{
"entryListId": 3279,
"id": 7455,
"name": "Signal Capital Partners",
"type": 0
}
\]

Мне нужно получить имена в таком формате вне зависимости от количества названий компаний: "СпецТра, Signal Capital Partners". Другими словами, мне нужно извлечь названия компаний и соединить их.

Я пробовал это:

regexp_replace(col, '"(\[^"\]+)"|.', '\\1|') 

и regexp_substr(), но не получил желаемого результата

Не могли бы вы помочь мне с этим? Спасибо

ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
1
0
42
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Таким образом, вы подталкиваете текстовый блок в CTE.

with data as (
    SELECT * FROM VALUES
    ('[{"entryListId": 3279,"id": 4617,"name": "SpecTra","type": 0},{"entryListId": 3279,"id": 7455,"name": "Signal Capital Partners","type": 0}]')
    t(str)
)

Я не могу не отметить, что это JSON, поэтому давайте PARSE_JSON это, а затем ВЫПОЛНИТЬ это, и вот вам «имена»

select 
    d.*
    ,f.value:name::text as name
from data d
    ,table(flatten(input=>parse_json(d.str))) f

давая:

СИЛНАЗВАНИЕ
[{"entryListId": 3279,"id": 4617,"name": "SpecTra","type": 0},{"entryListId": 3279,"id": 7455,"name": "Signal Capital Partners ","тип": 0}]СпецТра
[{"entryListId": 3279,"id": 4617,"name": "SpecTra","type": 0},{"entryListId": 3279,"id": 7455,"name": "Signal Capital Partners ","тип": 0}]Сигнал Капитал Партнерс

И таким образом агрегировать, используя ЛИСТАГГ

select 
    listagg(f.value:name::text, ',') as names
from data d
    ,table(flatten(input=>parse_json(d.str))) f

дает:

ИМЕНА
SpecTra, Signal Capital Partners

Дублирующиеся данные:

вы можете добавить DISTINCT в LISTAGG и сохранить только отдельные значения, но, учитывая, что это стоимость, я указал на это, и вы не упомянули дубликаты данных.

with data as (
    SELECT * FROM VALUES
    ('[
    {
"entryListId": 3279,
"id": 4617,
"name": "SpecTra",
"type": 0
},   
{
"entryListId": 3279,
"id": 4617,
"name": "SpecTra",
"type": 0
},
{
"entryListId": 3279,
"id": 7455,
"name": "Signal Capital Partners",
"type": 0
}]')
    t(str)
)
select 
    listagg(distinct f.value:name::text, ',') as names
from data d
    ,table(flatten(input=>parse_json(d.str))) f;

дает:

ИМЕНА
SpecTra, Signal Capital Partners

Где-как это решение регулярного выражения не обрабатывает этот случай:

with data as (
    SELECT * FROM VALUES
    ('[
    {
"entryListId": 3279,
"id": 4617,
"name": "SpecTra",
"type": 0
},   
{
"entryListId": 3279,
"id": 4617,
"name": "SpecTra",
"type": 0
},
{
"entryListId": 3279,
"id": 7455,
"name": "Signal Capital Partners",
"type": 0
}]')
    t(str)
)
select 
    trim(regexp_replace(regexp_replace(d.str, '"name":\\s*"([^"]+)"|.', '\\1,'), ',+', ','), ',') as regexp_replace
from data d

дает:

REGEXP_REPLACE
, , , СпецТра, , , , , , СпецТра, , , , , , Signal Capital Partners, ,

Добрый день Симеон! Большое спасибо за ответ! Да, ваши рассуждения тоже работают, но когда я сглаживаю и анализирую, некоторые строки дублируются. Есть ли способ избежать этого?

Ilkhom Oripov 06.04.2022 13:55

Я добавил ключевое слово для решения вашей проблемы, которое указано в документах, связанных с решением. Я также показываю, как rexexp_replace также не проходит тест на дублирование данных.

Simeon Pilgrim 06.04.2022 22:39
Ответ принят как подходящий

Ты можешь использовать

trim(regexp_replace(regexp_replace(col, '"name":\\s*"([^"]+)"|.', '\\1,'), ',+', ','), ',')

Подробности:

  • "name":\s*"([^"]+)"|. регулярное выражение соответствует "name":, затем ноль или более пробелов и ", а затем захватывает в группу 1 любой один или несколько символов, кроме ", а затем соответствует " символу и заменяет его группой 1 и запятой
  • Второй regexp_replace сжимает все запятые до одного вхождения запятой, ,+ соответствует одной или нескольким запятым (вместо этого вы также можете использовать более конкретный шаблон ,{2,})
  • trim удаляет запятые в начале и конце.

ах, приятно видеть, что ты избежал этого \s

Simeon Pilgrim 06.04.2022 10:45

Добрый день, Виктор! Большое спасибо за ваше решение, оно сработало! Но, поскольку я здесь новичок, я не знаю, как сделать ваш ответ решением.

Ilkhom Oripov 06.04.2022 13:53

@WiktorStribiżew, не могли бы вы также порекомендовать некоторые ресурсы для изучения регулярных выражений (например, специально для Snowflake, python и других), пожалуйста?

Ilkhom Oripov 06.04.2022 13:58

Виктор, а можно еще пояснить, что делают эти части? 1) [^"]+ 2) |. в шаблоне "имя":\s*"([^"]+)"|.

Ilkhom Oripov 06.04.2022 14:03

@IlkhomOripov для Snowflake мало ресурсов, так как он использует разновидность регулярных выражений POSIX. Для Python см. регулярные-выражения.info. Вы также можете подписаться на мой канал на ютубе (но я еще не добавил самые простые видео с регулярными выражениями).

Wiktor Stribiżew 06.04.2022 14:04

@IlkhomOripov [^"] — выражение с отрицательными скобками, соответствует любому одиночному символу, кроме ". + означает «повторить рисунок слева один или несколько раз». \s* соответствует нулю или более пробелам. | означает «ИЛИ». . соответствует любому символу, кроме символов разрыва строки (обычно в POSIX это может быть иначе).

Wiktor Stribiżew 06.04.2022 14:05

Другие вопросы по теме