MAPDELIMITEDEXTRACTOR

Extracts data with a delimiter character and other optional arguments, returning a single VMap value.

Syntax

MAPDELIMITEDEXTRACTOR (record-value [ USING PARAMETERS param=value[,...] ])

Arguments

record-value

String containing a JSON or delimited format record on which to apply the expression.

Parameters

delimiter

Single delimiter character.

Default: |

header_names

Delimiter-separated list of column header names.

Default: ucoln, where n is the column offset number, starting with 0 for the first column.

trim

Boolean, trim white space from header names and field values.

Default: true

treat_empty_val_as_null

Boolean, set empty fields to NULL rather than an empty string ('').

Default: true

Examples

These examples use a short set of delimited data:

Name|CITY|New city|State|zip
Tom|BOSTON|boston|MA|01
Eric|Burlington|BURLINGTON|MA|02
Jamie|cambridge|CAMBRIDGE|MA|08

To begin, save this data as delim.dat.

1. Create a flex table, dflex:

   => CREATE FLEX TABLE dflex();
   CREATE TABLE
   

2. Use COPY to load the delim.dat file. Use the flex tables fdelimitedparser with the header='false' option:

   => COPY dflex FROM '/home/release/kmm/flextables/delim.dat' parser fdelimitedparser(header='false');
    Rows Loaded
   -------------
            4
   (1 row)
   

3. Create a columnar table, dtab, with an identity id column, a delim column, and a vmap column to hold a VMap:

   => CREATE TABLE dtab (id IDENTITY(1,1), delim varchar(128), vmap long varbinary(512));
   CREATE TABLE
   

4. Use COPY to load the delim.dat file into the dtab table. MAPDELIMITEDEXTRACTOR uses the header_names parameter to specify a header row for the sample data, along with delimiter '!' :

   => COPY dtab(delim, vmap AS MAPDELIMITEDEXTRACTOR (delim
      USING PARAMETERS header_names='Name|CITY|New City|State|Zip')) FROM '/home/dbadmin/data/delim.dat'
   DELIMITER '!';
   
    Rows Loaded
   -------------
              4
   (1 row)
   

5. Use MAPTOSTRING for the flex table dflex to view the __raw__ column contents. Notice the default header names in use (ucol0 – ucol4), since you specified header='false' when you loaded the flex table:

   => SELECT MAPTOSTRING(__raw__) FROM dflex limit 10;
                                 maptostring
   -------------------------------------------------------------------------------------
    {
      "ucol0" : "Jamie",
      "ucol1" : "cambridge",
      "ucol2" : "CAMBRIDGE",
      "ucol3" : "MA",
      "ucol4" : "08"
   }
   
    {
      "ucol0" : "Name",
      "ucol1" : "CITY",
      "ucol2" : "New city",
      "ucol3" : "State",
      "ucol4" : "zip"
   }
   
    {
      "ucol0" : "Tom",
      "ucol1" : "BOSTON",
      "ucol2" : "boston",
      "ucol3" : "MA",
      "ucol4" : "01"
   }
   
    {
      "ucol0" : "Eric",
      "ucol1" : "Burlington",
      "ucol2" : "BURLINGTON",
      "ucol3" : "MA",
      "ucol4" : "02"
   }
   
   (4 rows)
   

6. Use MAPTOSTRING again, this time with the dtab table's vmap column. Compare the results of this output to those for the flex table. Note that MAPTOSTRING returns the header_name parameter values you specified when you loaded the data:

   => SELECT MAPTOSTRING(vmap) FROM dtab;
                                                         maptostring
   ------------------------------------------------------------------------------------------------------------------------
    {
      "CITY" : "CITY",
      "Name" : "Name",
      "New City" : "New city",
      "State" : "State",
      "Zip" : "zip"
   }
   
    {
      "CITY" : "BOSTON",
      "Name" : "Tom",
      "New City" : "boston",
      "State" : "MA",
      "Zip" : "02121"
   }
   
    {
      "CITY" : "Burlington",
      "Name" : "Eric",
      "New City" : "BURLINGTON",
      "State" : "MA",
      "Zip" : "02482"
   }
   
    {
      "CITY" : "cambridge",
      "Name" : "Jamie",
      "New City" : "CAMBRIDGE",
      "State" : "MA",
      "Zip" : "02811"
   }
   
   (4 rows)
   

7. Query the delim column to view the contents differently:

   => SELECT delim FROM dtab;
                   delim
   -------------------------------------
    Name|CITY|New city|State|zip
    Tom|BOSTON|boston|MA|02121
    Eric|Burlington|BURLINGTON|MA|02482
    Jamie|cambridge|CAMBRIDGE|MA|02811
   (4 rows)
   

See also

• MAPJSONEXTRACTOR
• MAPREGEXEXTRACTOR