CODEWITHSUNDEEP
powerbiodbcmarklogictde
MAZ
MAZ

Reputation: 11

Pulling MarkLogic template view data

I am new to TDE. For the document below, I ended up developing 5 templates and then was able to write a JOIN query (below). I pulled all document data by linking the views via __docid fragment ID.

It works fine when run in Query Console. However, when I tried to pull the same data say to PowerBI via ODBC then I cannot write the query because __docid is not getting passed.

Here are my questions:

  1. How can I assign __docid value to a view field?
  2. If not possible, can I create a single template for the document?
  3. Any other solution?

Thanks in advance.

URI: /json/2017.04.27_ID_NA_SL/chambers_2730.json

Document:

{
    "class": "sanction", 
    "sanction":                                       ==> Template 1
    {
    "batch": "2017.04.27_ID_NA_SL", 
    "id": "2017.04.27_IN_NA_SL/chambers_2730", 
    "date_board_order": "2017-04-27T00:00:00", 
    "date_effective": null, 
    "decision": null, 
    "reasoning": null, 
    "pas_code": null, 
    "method": "web", 
    "orig": "results/results_04_27_2017_04_50PM/ID_SummaryList_03_04PM_February_27_2017/ID-John_chambers-    04_27_2017_BO.pdf", 
    "professional":                                    ==> Template 2
    {
    "name_first": "John", 
    "name_middle": null, 
    "name_last": "chambers", 
    "license": null, 
    "me": "0499999999"
    }
    }
    , 
    "app":
    {
    "assignment":                                     ==> Template 3
    {
    "me": "Jessica Hernendez", 
    "pas": "Jessica Hernendez"
    }
    , 
    "status":                                         ==> Template 4
    {
    "state": "complete", 
    "me_complete": "true", 
    "pas_complete": "true"
    }
    , 
    "meta":                                           ==> Template 5
    {
    "alert": null, 
    "note": null
    }
    }
}

Query:

SELECT t.__docid, p.name_first, p.name_middle, p.name_last, p.license, p.meta,  
s.batch,s.id,s.date_order,s.orig, a.me, t.state
FROM sanction s     
JOIN professional p ON s.__docid=p.__docid      
JOIN assignment a ON s.__docid = a.__docid      
JOIN status t ON s.__docid = t.__docid      
ORDER BY p.name_last

Upvotes: 1

Views: 58

Answers (1)

grtjn
grtjn

Reputation: 20414

I am not sure if you can literally insert the value of __docid into a TDE field, but you can use xdmp:node-uri(.) instead. That will return the database uri, which is guaranteed unique.

I do wonder if you need 5 templates though. Your data doesn't seem to have repeated elements, so why not create one wide view that holds all sanction data? You could consider it a special purpose view optimized for PowerBI, and save effort on unnecessary joins at runtime.

HTH!

Upvotes: 1

Related Questions