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

score 1 · Answer 1 · answered Sep 01 '23 at 04:51

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']   │
└────────────────┘

Thank you for answering, But it's not what I exactly mean. 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 — Axel HK, Sep 01 '23 at 09:18
That's an array of valid JSON object, but as a whole it's not valid JSON. So are you inputting each `{"id":"1", "metric":"11"}` as their own rows using something like JSONEachRow? (I'm trying to figure out what your table schema looks like.) — Rich Raposa, Sep 01 '23 at 14:02

score 0 · Answer 2 · answered Sep 02 '23 at 20:11

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]

clickhouse extract several values by same key

2 Answers2