Chapter 11. NoSQL (Not Only SQL) Support


  1. Declaration of NoSQL Types
  2. NoSQL Functions
  3. NoSQL sample

Declaration of Array Types

An array data type is named by appending square brackets ([]) to the data type name of the array elements, and all the elements are of the same data type. The array subscript numbers are written within square brackets. It uses a one-based numbering convention for arrays, that is, an array of n elements starts with array[1] and ends with array[n].
DECLARE v_pay_by_month decimal(12,2)[12];
set v_pay_by_month[2]=123.45;
select v_pay_by_month[2] as feb;
select v_pay_by_month;

Array Functions

  1. CAST(expression AS array): converts expression to array. For instance, cast('{20000, 25000, 25000, 25000}' as array).
  2. CONVERT(expression, data_type): converts expression to another data type (json and jsonb). For instance, convert('{{"breakfast", "consulting"}, {"meeting", "lunch"}}', array).
  3. to_array(expression): Returns the value as array. For instance, to_array('{{"breakfast", "consulting"}, {"meeting", "lunch"}}').
  4. array_assign(anyArrayElement,expression): Returns an array with supplied value. For instance, array_assign(to_array('{{"breakfast", "consulting"}, {"meeting", "lunch"}}')[2],to_array('{"lunch", "meeting"}')).
  5. array_ndims(anyArrayElement): Returns the number of dimensions of the array.
  6. SPLIT(string1, string2): split string1 according to delimiter string2, and return an String[] object (Types.ARRAY). Special SPLIT(expression,'') will return strings which contains only Letter and Digit. Special SPLIT(expression,null) will split string into length=1 strings, which is only Letter or Digit.
  7. CINT(arrayExpr): converts an array object to a int[] object (Types.ARRAY).
  8. CLNG(arrayExpr): converts an array object to a long[] object (Types.ARRAY).
  9. CDBL(arrayExpr): converts an array object to a double[] object (Types.ARRAY).
  10. VAL(arrayExpr): converts an array object to an Object[] object (Types.ARRAY) which contains numeric values.

Array sample

SELECT cast('{20000, 25000, 25000, 25000}' as array);
SELECT convert('{{"breakfast", "consulting"}, {"meeting", "lunch"}}', array);
select to_array('{{"meeting", "lunch"}, {"training", "presentation"}}');

select to_array('{{"meeting", "lunch"}, {"training", "presentation"}}')[1][2];

select array_assign(to_array('{{"breakfast", "consulting"}, {"meeting", "lunch"}}')[2],to_array('{"lunch", "meeting"}'));

Copyright © 2003-2019 Heng Xing Tian Tai Lab | All Rights Reserved. |