CODEWITHSUNDEEP
marklogicmarklogic-10marklogic-optic-api
marklogic_coder
marklogic_coder

Reputation: 1510

Optic API: How to split string with delimiter(comma) as a separate row

My optic query gives back a single row with one column named "activeIngredient," which holds a list of IDs separated by commas. Check out the JSON below.

[
    {
        "productId": 1234567899,
        "productName": "Test Product",
        "languageCode": "EN",
        "activeIngredient": "24622176,37690963,78121460",
        "updatedDate": "2021-08-12T10:42:21.6181006Z"
    }
]

I need to break down the ids from "activeIngredient" by using a comma to create separate rows.

I attempted to use op:map and loop through it, but it keeps giving me the last id, which is 78121460, for every row.

This is how I want my output to appear.

[
    {
        "productId": 1234567899,
        "productName": "Test Product",
        "languageCode": "EN",
        "activeIngredient": "24622176",
        "updatedDate": "2021-08-12T10:42:21.6181006Z"
    },
    {
        "productId": 1234567899,
        "productName": "Test Product",
        "languageCode": "EN",
        "activeIngredient": "37690963",
        "updatedDate": "2021-08-12T10:42:21.6181006Z"
    },
    {
        "productId": 1234567899,
        "productName": "Test Product",
        "languageCode": "EN",
        "activeIngredient": "78121460",
        "updatedDate": "2021-08-12T10:42:21.6181006Z"
    }
]

I am using MarkLogic v10.0-6.1.

I really appreciate any help you can offer.

Upvotes: 1

Views: 81

Answers (2)

Bryan Shalke
Bryan Shalke

Reputation: 21

Edit: Updated with suggestion from Mads

For ML 10, if you know an upper limit to the number of ingredients per product, you can use a join-cross-product to expand out the rows. Here is an example that supports up to 100 ingredients per product. Note that making this too big can affect query scaling and memory usage, so you may have to play with it.

xquery version "1.0-ml";
import module namespace op="http://marklogic.com/optic"
     at "/MarkLogic/optic.xqy";
import module namespace ofn="http://marklogic.com/optic/expression/fn"
     at "/MarkLogic/optic/optic-fn.xqy";

let $products := op:from-literals(
  map:new()
  => map:with("productId", 1234567899)
  => map:with("productName", "Test Product")
  => map:with("languageCode", "EN")
  => map:with("activeIngredient", "24622176,37690963,78121460")
  => map:with("updatedDate", "2021-08-12T10:42:21.6181006Z")
)

let $pivot := op:from-literals((1 to 100)! map:entry("index", .))

return op:join-cross-product($products, $pivot)
=> op:bind((
     op:as("activeIngredient",  ofn:subsequence(ofn:tokenize(op:col("activeIngredient"),  ","), op:col("index"), 1))
   ))
=> op:where(ofn:exists(op:col("activeIngredient")))
=> op:result()
languageCode index productName updatedDate activeIngredient productId
EN 3 Test Product 2021-08-12T10:42:21.6181006Z 78121460 1234567899
EN 2 Test Product 2021-08-12T10:42:21.6181006Z 37690963 1234567899
EN 1 Test Product 2021-08-12T10:42:21.6181006Z 24622176 1234567899

Upvotes: 1

Mads Hansen
Mads Hansen

Reputation: 66781

You can use op:bind() to modify the activeIngredient column and change it from a CSV to a tokenized set of values, and then use op:unnest-inner() to produce a row for each of those values:

xquery version "1.0-ml";
import module namespace op="http://marklogic.com/optic"
     at "/MarkLogic/optic.xqy";
import module namespace ofn="http://marklogic.com/optic/expression/fn"
     at "/MarkLogic/optic/optic-fn.xqy";

op:from-literals(
  map:new()
  => map:with("productId", 1234567899)
  => map:with("productName", "Test Product")
  => map:with("languageCode", "EN")
  => map:with("activeIngredient", "24622176,37690963,78121460")
  => map:with("updatedDate", "2021-08-12T10:42:21.6181006Z")
) 
=> op:bind((
     op:as("activeIngredient",  ofn:tokenize(op:col("activeIngredient"),  ","))
   ))
=> op:unnest-inner('activeIngredient','activeIngredient')   
=> op:result()

Which produces:

languageCode productName updatedDate activeIngredient productId
EN Test Product 2021-08-12T10:42:21.6181006Z 24622176 1234567899
EN Test Product 2021-08-12T10:42:21.6181006Z 37690963 1234567899
EN Test Product 2021-08-12T10:42:21.6181006Z 78121460 1234567899

Upvotes: 1

Related Questions