I’m trying to use SQL in DBeaver to query a column containing some JSON values.

The JSON object has the following structure:

[

{“key”:”screen_name”, “value”:{“string_val”:”Dashboard”,”int_val”:1} },

{“key”:”screen_type”, “value”:{“string_val”:”Page1″, “int_val”:2} },

…

]

Let’s say, I’d like to extract the screen name “Dashboard”; how do I do that?

I’ve tried:

SELECT get_json_object(mycolumn, '$.key')

SELECT get_json_object(mycolumn, '$.value')

SELECT get_json_object(mycolumn, '$.screen_name')

SELECT get_json_object(mycolumn, '$.key.screen_name')

SELECT get_json_object(mycolumn, '$.key.screen_name.value')

SELECT get_json_object(mycolumn, '$.key.screen_name.string_val')

SELECT get_json_object(mycolumn, '$.key.screen_name.value.string_val')

SELECT get_json_object(mycolumn, '$.screen_name.value')

SELECT get_json_object(mycolumn, '$.screen_name.string_val')

SELECT get_json_object(mycolumn, '$.screen_name.value.string_val')

None of these worked (they output [NULL] in the SQL output).

Does anyone know how to do so? Thanks!