У меня есть эта строка в столбце 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(), но не получил желаемого результата
Не могли бы вы помочь мне с этим? Спасибо
Таким образом, вы подталкиваете текстовый блок в 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, , |
Я добавил ключевое слово для решения вашей проблемы, которое указано в документах, связанных с решением. Я также показываю, как rexexp_replace также не проходит тест на дублирование данных.
Ты можешь использовать
trim(regexp_replace(regexp_replace(col, '"name":\\s*"([^"]+)"|.', '\\1,'), ',+', ','), ',')
Подробности:
"name":\s*"([^"]+)"|.
регулярное выражение соответствует "name":
, затем ноль или более пробелов и "
, а затем захватывает в группу 1 любой один или несколько символов, кроме "
, а затем соответствует "
символу и заменяет его группой 1 и запятойregexp_replace
сжимает все запятые до одного вхождения запятой, ,+
соответствует одной или нескольким запятым (вместо этого вы также можете использовать более конкретный шаблон ,{2,}
)trim
удаляет запятые в начале и конце.ах, приятно видеть, что ты избежал этого \s
Добрый день, Виктор! Большое спасибо за ваше решение, оно сработало! Но, поскольку я здесь новичок, я не знаю, как сделать ваш ответ решением.
@WiktorStribiżew, не могли бы вы также порекомендовать некоторые ресурсы для изучения регулярных выражений (например, специально для Snowflake, python и других), пожалуйста?
Виктор, а можно еще пояснить, что делают эти части? 1) [^"]+ 2) |. в шаблоне "имя":\s*"([^"]+)"|.
@IlkhomOripov для Snowflake мало ресурсов, так как он использует разновидность регулярных выражений POSIX. Для Python см. регулярные-выражения.info. Вы также можете подписаться на мой канал на ютубе (но я еще не добавил самые простые видео с регулярными выражениями).
@IlkhomOripov [^"]
— выражение с отрицательными скобками, соответствует любому одиночному символу, кроме "
. +
означает «повторить рисунок слева один или несколько раз». \s*
соответствует нулю или более пробелам. |
означает «ИЛИ». .
соответствует любому символу, кроме символов разрыва строки (обычно в POSIX это может быть иначе).
Добрый день Симеон! Большое спасибо за ответ! Да, ваши рассуждения тоже работают, но когда я сглаживаю и анализирую, некоторые строки дублируются. Есть ли способ избежать этого?