search specific value in JSON array then merge as XML

user_16883181 0
Viewed 540

I just start this search JSON in different databases and transform:

/doc1.json:

{
    "seller": "s1",
    "product": [
        "football",
        "basketball"
    ],
    "sales": [
        {
            "football": 60,
            "basketball": [
                {
                    "c1": 76,
                    "c2": 90
                }
            ]
        }
    ]
}

/doc2.json

{
    "seller": "s2",
    "product": ["football"],
    "sales": [
        {"football": 80}
    ]
}

I find the product only contains one specific item (I will be better if JavaScript), merged with sales, and transform the result as XML (using XSLT ?). But I didn’t get very far. First, I try MarkLogic JS:

cts.search(cts.jsonPropertyValueQuery('product', 'football'))

and many other methods, it always returns 2 docs. I need to use module function instead of hardcoded JSON value.

Thanks in advance for any help.

var doc = cts.doc("/doc1.json");
 var foo = fn.head(xdmp.unquote(
    `
    <xsl:transform xmlns:xsl="http://www.w3.org/1999/XSL/Transform"\n\
        version="2.0">\n\

        <xsl:template match="/">\n\
            <root>\n\
                <xsl:element name="products">\n\
                    <xsl:for-each select="product">\n\
                        <xsl:element name="product">\n\
                            <xsl:variable name="value" select="."/>\n\
                            <xsl:value-of select="."/>\n\
                            <xsl:element name="sales">\n\
                                <xsl:for-each select="/sales/*[name() = $value]">\n\
                                    <xsl:copy select="."/>\n\
                                </xsl:for-each>\n\
                            </xsl:element>\n\
                        </xsl:element>\n\
                    </xsl:for-each>\n\
                </xsl:element>\n\
            </root>\n\
        </xsl:template>\n\

        <xsl:template match="@*|node()">\n\
            <xsl:copy>\n\
                <xsl:apply-templates select="@*|node()"/>\n\
            </xsl:copy>\n\
        </xsl:template>\n\

    </xsl:transform>\n\
    `));

    xdmp.xsltEval(foo, doc);

/doc2.json should be :

<root>
  <products>
    <product>football<sales>80</sales>       
    </product>
  </products>
</root>

/doc1.json should be :

<root>
  <products>
    <product>football<sales>60</sales></product>
    <product>basketball<sales>
      <c1>76</c1>
      <c2>90</c2>
    </sales></product>
  </products>
</root>
nosql xquery xslt json javascript
1 Answers

SJS:

function valueGetDocUri (collection, propertyPath, propertyValue) {
    const result =[];
    cts.uris("", null, cts.collectionQuery(collection)).toArray().forEach(uri => {
        (cts.values(cts.pathReference(propertyPath), null, null, cts.documentQuery(uri)) == propertyValue) ? result.push(uri) : {}
    });
    return result;
};

Execution (here I have the path index on /indicator):

const try3 = ['profile',  '/indicator',  'SMA'];

valueGetDocUri(...try3)

In MarkLogic, each JSON array value is a value of its associated property. cts.values + cts.pathReference will search the path containing only the specified value lexicon, in this case 'SMA'.

Sample documents:

  • /profile/multi-indicator.json
{
    "symbol": "USDEUR",
    "date": "2021-08-17",
    "indicator": [
        "BBANDS",       
        "SMA",
        "MACD"
    ] ,
    "riskAdjusted": [
        { "indicator": [  
              {
                "Treynor": 7.19,
                "Jensen": 5.13
              }
           ]
        }
    ],
    "technicalAnalysis": [
        {
            "SMA": 0.8426,
            "MACD": [
                {
                    "MACD_Signal": -0.0007,
                    "MACD_Hist": 0.0026,
                    "MACD": 0.0020
                }
            ],
            "BBANDS": [
                {
                    "realLowerBand": 0.8379,
                    "realUpperBand": 0.8593,
                    "realMiddleBand": 0.8486
                }
            ]
        }
    ]
}

To solve the 2nd problem (in your case, replace the params object with yours: match = ‘product’; merge = ‘sales’…etc)

let doc = cts.doc("/profile/multi-indicator.json");
var params = {};
params.match = 'indicator';
params.merge = 'technicalAnalysis'; 

const implJtoX = fn.head(xdmp.unquote(
`
<xsl:transform xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns="schema://fc.fasset/profile"
exclude-result-prefixes="xs" 
version="2.0">

<xsl:param name="match"/>
<xsl:param name="merge"/>

<xsl:template match="/">
    <profile>
        <xsl:apply-templates/>
        <xsl:element name="algorithm">
            <xsl:for-each select="*[name() eq $match]">
                <xsl:element name="indicator">
                    <xsl:variable name="eName" select="normalize-space(data(.))"/>
                    <xsl:value-of select="$eName"/>
                    <xsl:element name="technical">
                        <xsl:for-each
                            select="/*[name() eq $merge]/*[name() eq $eName]">
                            <xsl:choose>
                                <xsl:when test="*">
                                    <xsl:for-each select="./node()">
                                        <xsl:element name="{normalize-space(name(.))}">
                                            <xsl:value-of select="."/>
                                        </xsl:element>
                                    </xsl:for-each>
                                </xsl:when>
                                <xsl:otherwise>
                                    <xsl:value-of select="."/>
                                </xsl:otherwise>
                            </xsl:choose> 
                        </xsl:for-each> 
                    </xsl:element>
                </xsl:element>
            </xsl:for-each>
        </xsl:element>
    </profile>
</xsl:template>

<xsl:template match="node()">
    <xsl:apply-templates/>
</xsl:template>

</xsl:transform>
`));

const JtoX = xdmp.xsltEval(implJtoX, doc, params);
JtoX

From engineering point of view, you invoke the JavaScript functions and XSL, passing in the iterated documents in the search results and the transform parameters. I leave you package the SJS and XSL modules. If you pull off this run-up, I don’t see why you shouldn’t try your hands at multi namespaces XSL transform (with xdmp:dialect="1.0-ml" [the onus on MarkLogic] or without [the onus on XSLT editor]). The transformed model is like:

<prof:profile xmlns:prof="schema://fc.fasset/profile" xmlns:meta="schema://fc.fasset/svm/meta">
  <meta:header>
    <meta:IDV1>/svm/d91810d-158-494-ad3-e5afc35a5.xml</meta:IDV1>
    <meta:symbol>USDEUR</meta:symbol>
    <meta:dateSeries>2021-08-17</meta:dateSeries>
    <meta:performance>
        <meta:Treynor>7.19</meta:Treynor>
        <meta:Jensen>5.13</meta:Jensen>
    </meta:performance>
  </meta:header>
  <prof:algorithm>
    <prof:indicator>BBANDS<prof:technical>
        <prof:realLowerBand>0.8379</prof:realLowerBand>
        <prof:realUpperBand>0.8593</prof:realUpperBand>
        <prof:realMiddleBand>0.8486</prof:realMiddleBand>
    </prof:technical></prof:indicator>
    <prof:indicator>SMA<prof:technical>0.8426</prof:technical></prof:indicator>
    <prof:indicator>MACD<prof:technical>
        <prof:MACD_Signal>-0.0007</prof:MACD_Signal>
        <prof:MACD_Hist>0.0026</prof:MACD_Hist>
        <prof:MACD>0.002</prof:MACD>
    </prof:technical></prof:indicator>
  </prof:algorithm>
</prof:profile>

Of course, the JavaScript module has its XQuery equivalent. They can interchangeably evaluate each other or XSL module. Minute difference between SJS and XQY invocation notwithstanding, XSLT is well suited to such XML transformation tasks.

user_123095190
Related