Functions for dictionaries

DictCreate

Construct an empty dictionary. Two arguments are passed: for a key and a value. Each argument specifies a string with the data type declaration or the type itself built by type functions. There are no dictionaries with an unknown key or value type in YQL. As a key, you can set a primitive data type, except for Yson and Json that may be optional or a tuple of them of a length of at least two.

Documentation for the type definition format.

Examples

  1. SELECT DictCreate(String, Tuple<String,Double?>);

For dictionaries - 图1

  1. SELECT DictCreate(Tuple<Int32?,String>, OptionalType(DataType("String")));

For dictionaries - 图2

SetCreate

Construct an empty set. An argument is passed: the key type that can be built by type functions. There are no sets with an unknown key type in YQL. As a key, you can set a primitive data type, except for Yson and Json that may be optional or a tuple of them of a length of at least two.

Documentation for the type definition format.

Examples

  1. SELECT SetCreate(String);

For dictionaries - 图3

  1. SELECT SetCreate(Tuple<Int32?,String>);

For dictionaries - 图4

DictLength

The count of items in the dictionary.

Examples

  1. SELECT DictLength(AsDict(AsTuple(1, AsList("foo", "bar"))));

For dictionaries - 图5

DictHasItems

Check that the dictionary contains at least one item.

Examples

  1. SELECT DictHasItems(AsDict(AsTuple(1, AsList("foo", "bar")))) FROM my_table;

For dictionaries - 图6

DictItems

Get dictionary contents as a list of tuples including key-value pairs (List<Tuplekey_type,value_type>).

Examples

  1. SELECT DictItems(AsDict(AsTuple(1, AsList("foo", "bar"))));
  2. -- [ ( 1, [ "foo", "bar" ] ) ]

For dictionaries - 图7

DictKeys

Get a list of dictionary keys.

Examples

  1. SELECT DictKeys(AsDict(AsTuple(1, AsList("foo", "bar"))));
  2. -- [ 1 ]

For dictionaries - 图8

DictPayloads

Get a list of dictionary values.

Examples

  1. SELECT DictPayloads(AsDict(AsTuple(1, AsList("foo", "bar"))));
  2. -- [ [ "foo", "bar" ] ]

For dictionaries - 图9

DictLookup

Get a dictionary element by its key.

Examples

  1. SELECT DictLookup(AsDict(
  2.     AsTuple(1, AsList("foo", "bar")),
  3.     AsTuple(2, AsList("bar", "baz"))
  4. ), 1);
  5. -- [ "foo", "bar" ]

For dictionaries - 图10

DictContains

Checking if an element in the dictionary using its key. Returns true or false.

Examples

  1. SELECT DictContains(AsDict(
  2.     AsTuple(1, AsList("foo", "bar")),
  3.     AsTuple(2, AsList("bar", "baz"))
  4. ), 42);
  5. -- false

For dictionaries - 图11

DictAggregate

Apply aggregation factory to the passed dictionary where each value is a list. The factory is applied separately inside each key.
If the list is empty, the aggregation result is the same as for an empty table: 0 for the COUNT function and NULL for other functions.
If the list under a certain key is empty in the passed dictionary, such a key is removed from the result.
If the passed dictionary is optional and contains NULL, the result is also NULL.

Arguments:

  1. Dictionary.
  2. Aggregation factory.

Examples

  1. SELECT DictAggregate(AsDict(
  2.     AsTuple(1, AsList("foo", "bar")),
  3.     AsTuple(2, AsList("baz", "qwe"))), 
  4.     AggregationFactory("Max"));
  5. -- {1 : "foo", 2 : "qwe" }

For dictionaries - 图12

SetIsDisjoint

Check that the dictionary doesn’t intersect by keys with a list or another dictionary.

So there are two options to make a call:

  • With the Dict<K,V1> and List<K> arguments.
  • With the Dict<K,V1> and Dict<K,V2> arguments.

Examples

  1. SELECT SetIsDisjoint(ToSet(AsList(1, 2, 3)), AsList(7, 4)); -- true
  2. SELECT SetIsDisjoint(ToSet(AsList(1, 2, 3)), ToSet(AsList(3, 4))); -- false

For dictionaries - 图13

SetIntersection

Construct intersection between two dictionaries based on keys.

Arguments:

  • Two dictionaries: Dict<K,V1> and Dict<K,V2>.
  • An optional function that combines the values from the source dictionaries to construct the values of the output dictionary. If such a function has the (K,V1,V2) -> U type, the result type is Dict<K,U>. If the function is not specified, the result type is Dict<K,Void>, and the values from the source dictionaries are ignored.

Examples

  1. SELECT SetIntersection(ToSet(AsList(1, 2, 3)), ToSet(AsList(3, 4))); -- { 3 }
  2. SELECT SetIntersection(
  3.     AsDict(AsTuple(1, "foo"), AsTuple(3, "bar")),
  4.     AsDict(AsTuple(1, "baz"), AsTuple(2, "qwe")),
  5.     ($k, $a, $b) -> { RETURN AsTuple($a, $b) });
  6. -- { 1 : ("foo", "baz") }

For dictionaries - 图14

SetIncludes

Checking that the keys of the specified dictionary include all the elements of the list or the keys of the second dictionary.

So there are two options to make a call:

  • With the Dict<K,V1> and List<K> arguments.
  • With the Dict<K,V1> and Dict<K,V2> arguments.

Examples

  1. SELECT SetIncludes(ToSet(AsList(1, 2, 3)), AsList(3, 4)); -- false
  2. SELECT SetIncludes(ToSet(AsList(1, 2, 3)), ToSet(AsList(2, 3))); -- true

For dictionaries - 图15

SetUnion

Constructs a union of two dictionaries based on keys.

Arguments:

  • Two dictionaries: Dict<K,V1> and Dict<K,V2>.
  • An optional function that combines the values from the source dictionaries to construct the values of the output dictionary. If such a function has the (K,V1?,V2?) -> U type, the result type is Dict<K,U>. If the function is not specified, the result type is Dict<K,Void>, and the values from the source dictionaries are ignored.

Examples

  1. SELECT SetUnion(ToSet(AsList(1, 2, 3)), ToSet(AsList(3, 4))); -- { 1, 2, 3, 4 }
  2. SELECT SetUnion(
  3.     AsDict(AsTuple(1, "foo"), AsTuple(3, "bar")),
  4.     AsDict(AsTuple(1, "baz"), AsTuple(2, "qwe")),
  5.     ($k, $a, $b) -> { RETURN AsTuple($a, $b) });
  6. -- { 1 : ("foo", "baz"), 2 : (null, "qwe"), 3 : ("bar", null) }

For dictionaries - 图16

SetDifference

Construct a dictionary containing all the keys with their values in the first dictionary with no matching key in the second dictionary.

Examples

  1. SELECT SetDifference(ToSet(AsList(1, 2, 3)), ToSet(AsList(3, 4))); -- { 1, 2 }
  2. SELECT SetDifference(
  3.     AsDict(AsTuple(1, "foo"), AsTuple(2, "bar")), 
  4.     ToSet(AsList(2, 3)));
  5. -- { 1 : "foo" }

For dictionaries - 图17

SetSymmetricDifference

Construct a symmetric difference between two dictionaries based on keys.

Arguments:

  • Two dictionaries: Dict<K,V1> and Dict<K,V2>.
  • An optional function that combines the values from the source dictionaries to construct the values of the output dictionary. If such a function has the (K,V1?,V2?) -> U type, the result type is Dict<K,U>. If the function is not specified, the result type is Dict<K,Void>, and the values from the source dictionaries are ignored.

Examples

  1. SELECT SetSymmetricDifference(ToSet(AsList(1, 2, 3)), ToSet(AsList(3, 4))); -- { 1, 2, 4 }
  2. SELECT SetSymmetricDifference(
  3.     AsDict(AsTuple(1, "foo"), AsTuple(3, "bar")),
  4.     AsDict(AsTuple(1, "baz"), AsTuple(2, "qwe")),
  5.     ($k, $a, $b) -> { RETURN AsTuple($a, $b) });
  6. -- { 2 : (null, "qwe"), 3 : ("bar", null) }

For dictionaries - 图18