Structural functions

This page goes over the structural functions in Upsolver.

FROM_KEY_VALUE

Maps a list of key values to a record with fields for each key.

Inputs

• key

• value

Properties

• Keys

Example

Input

{
  "data": [
    { "key": "a", "value": 1 },
    { "key": "b", "value": 2 }
  ]
}

SQL

SET result = FROM_KEY_VALUE('a,b', data[].key, data[].value)

Result

{
  "result": {
    "a": 1,
    "b": 2
  }
}

GET_RANGE

Returns range of numbers between start and end (inclusive).

Inputs

• first

• last

ITEM_INDEX

Gets the index of the item.

Properties

• Global Index - Use the global index in the event instead of the index in the containing array

• Count Nulls

JSON_PATH

Extracts data from JSON objects.

Inputs

• JSON - A String that contains JSON Document

Properties

• Path - JSON Path Expression

JSON	Path	Result
"[{ "name": "The Great Gatsby", "author": { "name": "F. Scott Fitzgerald" } }, { "name": "Nineteen Eighty-Four", "author": { "name": "George Orwell" } }]"	"$[*].author.name"	"F. Scott Fitzgerald", "George Orwell"
"{ "net_id": 41 }"	"net_id"	"41"
"{ "net_id": [41, 42] }"	"net_id"	"[41, 42]"
"{ "net_id": [41, 42] }"	"net_id[*]"	"41", "42"
"{ "net_id": [41, 42] }"	"net_id.parent"	null
"[1,2,3]"	"$[*]"	"1", "2", "3"
"[{ "name": "The Great Gatsby", "author": { "name": "F. Scott Fitzgerald" } }, { "name": "Nineteen Eighty-Four", "author": { "name": "George Orwell" } }]"	"$[*].author.name"	"{"name":"F. Scott Fitzgerald"}", "{"name":"George Orwell"}"

JSON_TO_RECORD

Extracts data from JSON objects. This only includes any JSON documents from the data source; this function does not work on or include in the result any calculated fields.

This function expects a string formatted according to classic JSON requirements.

See: JSON format

It is recommended that you first validate your string format before applying this function as additional pre-processing may be required to get your data in the correct format.

See the data below for an example.

Properties

• Mappings - JSON to field mappings

• Output Array - If the string contains multiple JSON records use this to allow outputing all of them

Value	Mappings	Output Array	Result
"{ "a": "Hello" }"	"a,a,string"	true	{"a": "Hello"}
"{ "a": { "value": "Hello" }, "b" : { "value": "World" } }"	"a.value,a.value,string b.value,b.value,string"	true	{"a.value": "Hello", "b.value": "World"}

Example

Sample data

[{'packageid': 7, 
  'percent_savings_text': ' ', 
  'percent_savings': 0, 
  'option_text': 'Counter-Strike: Condition Zero - 8,19€', 
  'option_description': '', 
  'can_get_free_license': '0', 
  'is_free_license': False, 
  'price_in_cents_with_discount': 819}, 
 {'packageid': 574941, 
  'percent_savings_text': ' ', 
  'percent_savings': 0, 
  'option_text': 
  'Counter-Strike - Commercial License - 8,19€', 
  'option_description': '', 
  'can_get_free_license': '0', 
  'is_free_license': False, 
  'price_in_cents_with_discount': 819}]

Notice that this data is not in standard JSON format as it uses single quotes ' instead of double quotes ".

Additionally, the boolean values are formatted as True and False; however, this function only parses lower case boolean values.

As a result, in order to correctly parse our original string, we first convert it to a proper JSON format before using using the JSON_TO_RECORD function to parse it.

SET partition_date = UNIX_EPOCH_TO_DATE(time);
SET clean_subs = TRIM(' [{''packageid'': 7, ''percent_savings_text'': '' '', ''percent_savings'': 0, ''option_text'': ''Counter-Strike: Condition Zero - 8,19€'', ''option_description'': '''', ''can_get_free_license'': ''0'', ''is_free_license'': False, ''price_in_cents_with_discount'': 819}, {''packageid'': 574941, ''percent_savings_text'': '' '', ''percent_savings'': 0, ''option_text'': ''Counter-Strike - Commercial License - 8,19€'', ''option_description'': '''', ''can_get_free_license'': ''0'', ''is_free_license'': False, ''price_in_cents_with_discount'': 819}]');
SET replacesubs = REGEXP_REPLACE(REGEXP_REPLACE(REPLACE('''', '"', clean_subs), ':\s*False', ':false'), ':\s*True', ':true');
SET jsonrecord = JSON_TO_RECORD('packageid,packageid,number
percent_savings_text, percent_savings_text,string
percent_savings,percent_savings,number
option_text,option_text,string
option_description,option_description,string
can_get_free_license,can_get_free_license,number
is_free_license,is_free_license,string
price_in_cents_with_discount, price_in_cents_with_discount, number',
 true,replacesubs);

SELECT jsonrecord[].packageid:number AS jsonrecord[].packageid:number,
       jsonrecord[].percent_savings_text:STRING AS jsonrecord[].percent_savings_text:STRING,
       jsonrecord[].percent_savings:number AS jsonrecord[].percent_savings:NUMBER,
       jsonrecord[].option_text:string AS jsonrecord[].option_text:STRING,
       jsonrecord[].option_description:string AS jsonrecord[].option_description:STRING,
       jsonrecord[].can_get_free_license:number AS jsonrecord[].can_get_free_license:number,
       jsonrecord[].is_free_license:string AS jsonrecord[].is_free_license:string
  FROM xyxz

If you have multiple records concatenated into one string as shown in the sample data, it is recommended to use Upsolver's JSON output format rather than the tabular output format. This allows the output to have an array column structure.

MAP_WITH_INDEX

Outputs an index and a value field. Index contains a zero based index and value contains the value in the input field.

Inputs

• value - The value to convert to a record

value	result
"a", "b", "c"	{"index": 0, "value": "a"}, {"index": 1, "value": "b"}, {"index": 2, "value": "c"}

The example below illustrates the usage of the function and also how to reference the array of values created as a result:

LET dlc_parsed = MAP_WITH_INDEX(SPLIT(TRIM_CHARS('[] ', dlc),',')) 
  , dlc_parsed[].game_hier_key = MD5(STRING_FORMAT('{0}.{1}.{2}'
                                   , appid 
                                   , dlc_parsed[].value, $event_date)))

QUERY_STRING_TO_RECORD

Extracts data from query string.

Properties

• Mappings - Field names

RECORD_TO_JSON

Converts the record containing the field to a JSON string. This only includes any JSON documents from the data source; this function does not work on or include in the result any calculated fields.

Note that the preview functionality only shows fields in the JSON that were output directly in other fields. However, when you run the output, it will include the full JSON.

Example

Input

{ "a": [{"b": 1, "c": 1}, {"b": 2, "c": 2}] }

SQL

SET jsons = RECORD_TO_JSON(a[].b);

Result

[{"b": 1, "c": 1}, {"b": 2, "c": 2}]

TO_ARRAY

Outputs the values from the inputs as an array.

inputs	result
["a", "c"], ["b"]	"a", "c", "b"

ZIP

See: ZIP