snowflake json retrieve tags with specific value

Question

Have values in a snowflake table like this:

{
  "value": "0",
  "_my_id1": "111",
  "_my_id2": "123",
  "some_tag": "random",
  "something_else": "random1",
  "seg": "123,234,234,444,555"
  "some_id": "000987",
  "plus_tag": "yes"
}

from this tag, need to retrieve all keys that contain "ID" in it, looking for results like:

  "_my_id1": "111",
  "_my_id2": "123",
  "some_id": "000987"

Result can be json or string

Answer 1

Using following as data -

with data_cte as
(
select column1 from values('{
"value": "0",
"_my_id1": "111",
"_my_id2": "123",
"some_tag": "random",
"something_else": "random1",
"seg": "123,234,234,444,555",
"some_id": "000987",
"plus_tag": "yes"
}')
)
select * from data_cte;
+-------------------------------+
| COLUMN1                       |
|-------------------------------|
| {                             |
| "value": "0",                 |
| "_my_id1": "111",             |
| "_my_id2": "123",             |
| "some_tag": "random",         |
| "something_else": "random1",  |
| "seg": "123,234,234,444,555", |
| "some_id": "000987",          |
| "plus_tag": "yes"             |
| }                             |
+-------------------------------+

Query to get all with ID key -

with data_cte as
(
select column1 from values('{
"value": "0",
"_my_id1": "111",
"_my_id2": "123",
"some_tag": "random",
"something_else": "random1",
"seg": "123,234,234,444,555",
"some_id": "000987",
"plus_tag": "yes"
}')
)
select key,value from data_cte,
lateral flatten(input=>parse_json(column1))
where key like '%id%';
+---------+----------+
| KEY     | VALUE    |
|---------+----------|
| _my_id1 | "111"    |
| _my_id2 | "123"    |
| some_id | "000987" |
+---------+----------+

Construct back JSON from above output as -

with data_cte as
(
select column1 from values('{
"value": "0",
"_my_id1": "111",
"_my_id2": "123",
"some_tag": "random",
"something_else": "random1",
"seg": "123,234,234,444,555",
"some_id": "000987",
"plus_tag": "yes"
}')
)
select to_json(object_construct(key,value)) as col from data_cte,
lateral flatten(input=>parse_json(column1))
where key like '%id%';
+----------------------+
| COL                  |
|----------------------|
| {"_my_id1":"111"}    |
| {"_my_id2":"123"}    |
| {"some_id":"000987"} |
+----------------------+

Output as String -

with data_cte as
(
select column1 from values('{
"value": "0",
"_my_id1": "111",
"_my_id2": "123",
"some_tag": "random",
"something_else": "random1",
"seg": "123,234,234,444,555",
"some_id": "000987",
"plus_tag": "yes"
}')
)
select '{'||listagg(key||':'||value,',')||'}' as col from data_cte,
lateral flatten(input=>parse_json(column1))
where key like '%id%';
+------------------------------------------+
| COL                                      |
|------------------------------------------|
| {_my_id1:111,_my_id2:123,some_id:000987} |
+------------------------------------------+