How can I identify records that are array in a varchar column?-CodePudding

I have a column with data type: varchar. Within this column there are several records that appear to be in an "array format" meaning, it's highlighted in BLUE and I can click on it to see more 'details'. Other records are just varchar/string type (when I click on these, I don't see a "detail" popup.

Is there a way I can identify these records that seem to be in array format?

CodePudding user response：

If the column is defined as a VARCHAR and it's returned in blue in the web UI, it's because it's multi-line, not because something in it looks like an array.

For example, this single-line VARCHAR will return in black:

select '[1,2,3]'; -- Returns in black

However, this non-array string shows in blue because it's multi-line:

select 'Hello\nworld'; -- Returns in blue.

When you click on the columns in blue, you can see more lines. This is why they're returning in blue, to identify which ones can be clicked on for more lines.

If your VARCHARs with arrays are displayed this way, it's probably because they're multi-line.

CodePudding user response：

To identify if a column is array type of not, you can use IS_ARRAY, which expects a VARIANT type:

SELECT IS_ARRAY('["1", "2"]'::VARIANT) as is_array;
 ---------- 
| IS_ARRAY |
|----------|
| False    |
 ---------- 

SELECT IS_ARRAY(PARSE_JSON('["1", "2"]')::VARIANT) as is_array;
 ---------- 
| IS_ARRAY |
|----------|
| True     |
 ---------- 

SELECT IS_ARRAY(PARSE_JSON('{"1": "2"}')::VARIANT) as is_array;
 ---------- 
| IS_ARRAY |
|----------|
| False    |
 ---------- 

-- you can use TRY_PARSE_JSON in case you do not know the format of the data
-- if it fails, it will return NULL, and IS_ARRAY on NULL will be NULL too
SELECT IS_ARRAY(TRY_PARSE_JSON('{"1: "2"}')::VARIANT) as is_array;
 ---------- 
| IS_ARRAY |
|----------|
| NULL     |
 ----------

https://docs.snowflake.com/en/sql-reference/functions/is_array.html