[SOLVED] Search comparing and searching for strings in a variant table, trouble with correct construct for varient type

Search comparing and searching for strings in a variant table, trouble with correct construct for varient type

So I am following the example in the documentation here as I am trying to compare the json strings that I have uploaded from the tutorial, but am having troubles searching the variant type I uploaded my json file into.

So for the basic understanding I tried:

USE DATABASE MYDB; 
USE WAREHOUSE MYWH; 


create table demonstration1 (
    id integer,
    array1 array,
    variant1 variant,
    object1 object
    );

insert into demonstration1 (id, array1, variant1, object1) 
  select 
    1, 
    array_construct(1, 2, 3), 
    parse_json(' { "key1": "value1", "key2": "value2" } '),
    parse_json(' { "outer_key1": { "inner_key1A": "1a", "inner_key1B": "1b" }, '
              ||
               '   "outer_key2": { "inner_key2": 2 } } ')
    ;

insert into demonstration1 (id, array1, variant1, object1) 
  select 
    1, 
    array_construct(1, 2, 3, null), 
    parse_json(' { "key1": "value1", "key2": NULL } '),
    parse_json(' { "outer_key1": { "inner_key1A": "1a", "inner_key1B": NULL }, '
              ||
               '   "outer_key2": { "inner_key2": 2 } '
              ||
               ' } ')
  ;
  
  select variant1  from demonstration1
  where variant1 contains('value');

error was that it did not recognize contains: SQL compilation error: syntax error line 2 at position 17 unexpected 'contains'.

This did not work either when I tried to use Array_contains:

ARRAY_CONTAINS('value'::variant, array_construct(variant1)) from demonstration1;

What should I be trying?

Solution

The syntax for contains is a bit different. Try this instead:

select variant1 from demonstration1 where contains(variant1, 'value2');