How to extract the word followed by 'list' in a string in clickhouse

Kay Jey 0
Viewed 29

My data in column foo looks like this

row 1 :[{\"id\": 20, \"list\": [\"NEWYORK\", \"Rajasthan\", \"Spain\", \"Delhi\"], \"var\": 20}]

row 2 :[{\"id\": 23, \"list\": [\"China\", \"tokyo\", \"Spain\", \"Mumbai\"], \"var\": 25}]

I want to extract the first element in the 'list', eg ,NEWYORK , China , etc..

How do I extract it in clickhouse.

clickhouse regex
1 Answers

As I understood the 'string' is JSON, so I would use the Functions for Working with JSON to parse it:

WITH ['[{"id": 20, "list": ["NEWYORK", "Rajasthan", "Spain", "Delhi"], "var": 20}]', '[{"id": 23, "list": ["China", "tokyo", "Spain", "Mumbai"], "var": 25}]'] AS data
SELECT
    arrayJoin(data) AS row,
    JSONExtract(row, 1, 'list', 1, 'String') AS result

/*
┌─row─────────────────────────────────────────────────────────────────────────┬─result──┐
│ [{"id": 20, "list": ["NEWYORK", "Rajasthan", "Spain", "Delhi"], "var": 20}] │ NEWYORK │
│ [{"id": 23, "list": ["China", "tokyo", "Spain", "Mumbai"], "var": 25}]      │ China   │
└─────────────────────────────────────────────────────────────────────────────┴─────────┘
*/
userwVg10
Related