clickhouse extract several values by same key

Question

I have a string which represent array of jsons like '[{json}, {json}...]' in this jsons I have similar keys like metric: '[{"metric": "", }, {"metric": "", }]'. How can I get all values by key "metric" ? If I will use JsonExtract it will return nothing because of repeatable keys in string.

The main point is in a string which represent array of json's objects I have same keys. And I need to get them. As example: '[{"id":"1", "metric":"11"}, {"id":"1", "metric":"12"}]'. So I want to get by key "metric" values 11, 12

Answer 1

Try this:

SELECT 
  JSONExtractInt (j, 'id') AS id
, groupArray (JSONExtractInt (j, 'metric')) AS metrics
FROM
(
  SELECT arrayJoin (JSONExtractArrayRaw (c1)) AS j
  FROM VALUES 
  (
      ('[{"id":"1", "metric":"11"}, {"id":"1", "metric":"12"}]')
    , ('[{"id":"2", "metric":"21"}, {"id":"2", "metric":"22"}, {"id":"2", "metric":"23"}]')
  )
)
GROUP BY id

id	metrics
2	[21,22,23]
1	[11,12]

Answer 2

I'm not sure what your data looks like, so I'm making an assumption that it is valid JSON and looks similar to the following. If not, let me know - but this should get you headed in the right direction:

SET allow_experimental_object_type = 1;

CREATE TABLE IF NOT EXISTS metric_json (
    raw_data String EPHEMERAL,
    id String DEFAULT JSONExtractString(raw_data, 'id'),
    metrics Array(JSON) DEFAULT JSONExtractArrayRaw(raw_data, 'metrics')
) ENGINE = MergeTree
ORDER BY id;


INSERT INTO metric_json (raw_data) VALUES 
    ('{"id":"1", "metrics": [{"metric":"11"},{"metric":"101"}]}'),
    ('{"id":"2", "metrics": [{"metric":"22"},{"metric":"202"}]}');
    
SELECT metrics.metric FROM metric_json;

The response looks like:

┌─metrics.metric─┐
│ ['11','101']   │
│ ['22','202']   │
└────────────────┘