<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Miroslav Mihaylov on Medium]]></title>
        <description><![CDATA[Stories by Miroslav Mihaylov on Medium]]></description>
        <link>https://medium.com/@mirkenstein_23797?source=rss-f8ee9500496f------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*T_UnOcBt0TR9fh8q8yZgRg.jpeg</url>
            <title>Stories by Miroslav Mihaylov on Medium</title>
            <link>https://medium.com/@mirkenstein_23797?source=rss-f8ee9500496f------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Tue, 17 Nov 2026 06:00:02 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@mirkenstein_23797/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Healthcare Payer Price Transparency with Couchbase and N1QL]]></title>
            <link>https://medium.com/@mirkenstein_23797/healthcare-payer-price-transparency-with-couchbase-and-n1ql-758cc9336778?source=rss-f8ee9500496f------2</link>
            <guid isPermaLink="false">https://medium.com/p/758cc9336778</guid>
            <category><![CDATA[n1ql]]></category>
            <category><![CDATA[couchbase]]></category>
            <category><![CDATA[nosql]]></category>
            <category><![CDATA[healthcare]]></category>
            <category><![CDATA[medical-prices]]></category>
            <dc:creator><![CDATA[Miroslav Mihaylov]]></dc:creator>
            <pubDate>Fri, 20 Jan 2023 05:49:11 GMT</pubDate>
            <atom:updated>2023-01-20T05:49:11.582Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*dYtr1-v3JA282KXC" /><figcaption>Photo by <a href="https://unsplash.com/@gtomassetti?utm_source=medium&amp;utm_medium=referral">Giorgio Tomassetti</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><h3>CMS Transparency in Coverage Final Rule</h3><p>Recently CMS (Centers for Medicare &amp; Medicaid Services) mandated that all health plans and health insurance issuers post their rate information for all covered services between the plan issuer and the in-network provider. One of the goal of this mandate as stated on the Health Plan Transparency page <a href="https://www.cms.gov/healthplan-price-transparency">https://www.cms.gov/healthplan-price-transparency</a> is</p><blockquote>“…provide additional access to pricing information and enhance consumers’ ability to shop for the health care that best meet their needs”</blockquote><p>The technical implementation guide</p><p><a href="https://github.com/CMSgov/price-transparency-guide">GitHub - CMSgov/price-transparency-guide: The technical implementation guide for the tri-departmental price transparency rule.</a></p><p>defines the data structure and methods under which the issuing entity (the health plans) should publish the files.</p><p>These rate files called MRF ( machine-readable files ) are in JSON or XML format and contains costs for services with the references to the providers that are rendering these services. They are posted on the websites of the various issuing entities.</p><h3>MRF File Schema</h3><p>The Machine Readable Files files can be as large as tens of Gigabytes each and sometimes they can exceed Terabyte. The files schema describing the data format are posted on the CMS <a href="https://github.com/CMSgov/price-transparency-guide/tree/master/schemas/in-network-rates">https://github.com/CMSgov/price-transparency-guide/tree/master/schemas/in-network-rates</a></p><p>Top level files structure looks like this</p><pre>{&quot;reporting_entity_name&quot;:&quot;Some BlueCross BlueShield&quot;,<br>&quot;reporting_entity_type&quot;:&quot;Health Insurance Network&quot;,<br>&quot;last_updated_on&quot;:&quot;2022-12-01&quot;,<br>&quot;version&quot;:&quot;1.2.3&quot;,<br>&quot;provider_references&quot;:[ ... ],<br>&quot;in_network&quot;: [... ]<br>}</pre><p>Where the in_network list contains the rate information for the various medical codes labeled as billing_code. Here is a what sample for a single item from that list look like:</p><pre>{<br>  &quot;negotiation_arrangement&quot;: &quot;ffs&quot;,<br>  &quot;name&quot;: &quot;AMB SERVICE OUTSIDE STATE PER MILE TRANSPORT&quot;,<br>  &quot;billing_code_type&quot;: &quot;HCPCS&quot;,<br>  &quot;billing_code_type_version&quot;: &quot;2022&quot;,<br>  &quot;billing_code&quot;: &quot;A0021&quot;,<br>  &quot;description&quot;: &quot;Ambulance service, outside state per mile, transport (Medicaid only)&quot;,<br>  &quot;negotiated_rates&quot;: [<br>    {<br>      &quot;provider_references&quot;: [41354,9436,35161],<br>      &quot;negotiated_prices&quot;: [<br>        {<br>          &quot;negotiated_rate&quot;: 10.29,<br>          &quot;service_code&quot;: [  &quot;20&quot;,&quot;23&quot;,&quot;41&quot;,&quot;42&quot; ],<br>          &quot;negotiated_type&quot;: &quot;negotiated&quot;,<br>          &quot;expiration_date&quot;: &quot;9999-12-31&quot;,<br>          &quot;billing_class&quot;: &quot;professional&quot;,<br>          &quot;billing_code_modifier&quot;: [&quot;GM&quot;,&quot;JY&quot;],<br>          &quot;additional_information&quot;: &quot;&quot;<br>        }<br>      ]<br>    },<br>    {<br>      &quot;provider_references&quot;: [...],<br>      &quot;negotiated_prices&quot;: [<br>        {<br>        ...<br>        }<br>      ]<br>    },<br>  <br>    ....<br>}</pre><p>The negotiated_rates list would typically contain many items. It can be as large as tens of thousands and in some instances it can reach hundreds of thousands of items. One of the many nested attributes in the negotiated_rates is the provider_references which is a list of integers, each representing a reference to provider group. The reference for these provider groups are contained in the other top level list provider_references which looks like this.</p><pre>{<br>&quot;provider_group_id&quot;: 41354<br>  &quot;provider_groups&quot;: [<br>    {<br>      &quot;npi&quot;: [  1881780344, 1609829100, 1225090087  ],<br>      &quot;tin&quot;: {<br>        &quot;type&quot;: &quot;ein&quot;,<br>        &quot;value&quot;: &quot;123456789&quot;<br>      }<br>    }<br>  ]<br> }</pre><p>So the provider_group_id fields from the provider_references is the group id which was one of the list items in provider_references from the in_network.negotiated_rates</p><h3>Insights from the MRF files</h3><p>These are large datasets with complex JSON structures with many nested lists each of which having additional nested objects and lists. If we were to extract insights from these files we would need to put them in some sort of a database. This process would take 3 steps:</p><ol><li>Split each MRF file on two lists for in_network and provider_references</li><li>Unnest or flatten the top level lists to some degree and perhaps further flatten some of their children items</li><li>Ingest the data into some Relational Database System with support for JSON fields or perhaps NoSQL Database System to query the processed data</li></ol><p>Considering the large size of individual files and the complex data structure we can expect that major effort will be spent on the development of the application or data pipeline that would process the file. Further more depending on the type of insights one might want to extract from the MRF files would lead to a different set of flattening and it is very likely to find ourselves running trough additional iterations on step 2 above based on the questions we ask. Fully UNNESTING the documents and lists does not seems that good of an idea either since that could easily expand each “line” to tens of millions of lines.</p><p>Finally regardless of the questions we would like to answer, we most certainly would need some sort of SQL JOIN across both top level list in_network.negotiated_rates[..]provider_references and provider_references.provider_group_id</p><p>It would be nice if we we could just grab the original files without any modification and ingest them directly. That would save us some electrons on the computational part.</p><p>If only there were a database system that support ANSI SQL JOINS on these complex JSON structures,… that would be great!</p><h3>Having the cake and eating it too</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/893/1*Ne8cqzVhxQsa7yjIYnKjpQ.jpeg" /></figure><p>More than 4 years ago I worked with the <a href="https://www.couchbase.com/">Couchbase</a> NoSQL database system. When I saw that complex data structures I thought of Couchcbase because of the one unique feature that they have, namely the ANSI SQL JOINS across JSON documents and collections via their <a href="https://www.couchbase.com/products/n1ql">N1QL (nickel)</a> product. Being distributed database system its indexes also support <a href="https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/index-partitioning.html">partitioning</a>.</p><p>I had some spare time over the New Years holiday break and decided to see how far would <a href="https://www.couchbase.com/products/n1ql">N1QL</a> really go without any modification of the original data structure, but simply using the <a href="https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/unnest.html">UNNEST clause</a> and <a href="https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/join.html">JOIN</a>.</p><p>For the rest of this blog post I will share my experimentation with Couchbase and MRF files.</p><h3>Trying Couchbase with Docker First</h3><p>At first I took a small (Less than 100 Megabytes in size) MRF file from one of the many health plans out there and split the two top level list via the <a href="https://stedolan.github.io/jq/">jq</a> command line utility.</p><pre>jq -c &#39;.in_network  &#39;          $mrf_file_name &gt;  in_network.json <br>jq -c &#39;.provider_references  &#39; $mrf_file_name &gt;  prov_ref.json</pre><p>The output files are basically JSON lists of the two MRF components.</p><p>Launche a single node docker container on my local machine as described in the official documentation</p><p><a href="https://docs.couchbase.com/server/current/install/getting-started-docker.html">https://docs.couchbase.com/server/current/install/getting-started-docker.html</a></p><pre>docker run -d --name db -p 8091-8096:8091-8096 -p 11210-11211:11210-11211 couchbase</pre><p>Side note: I also installed Couchbase on my local Fedora workstation by downloading the RPM for RHEL8 and the prerequisite package <strong>ncurses-compat-libs.x86_64</strong> .</p><p>Navigate with your browser to <a href="http://localhost:8091/">http://localhost:8091 </a>and we are presented with the screen</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*RCEBXotaeE02jx3irf7HCg.png" /></figure><p>Click on “Setup a new Cluster” and proceed through the setup step to create the single node cluster using the default values everywhere.</p><p>Next in the web console we create a bucket (much like database ) called pt_bucket and add the scope payer (db schema) and in that scope we add the two collections (aka tables)in_network and provider_references which will store the individual JSON documents ( lines ).</p><p><a href="https://docs.couchbase.com/server/current/manage/manage-scopes-and-collections/manage-scopes-and-collections.html">Manage Scopes and Collections</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ajLJ4zfMkHCUwTyxpwAVJA.png" /></figure><p>Now we are ready to import our sample data. Since the two files are small we can copy them to the container image so that we can import them from there using the command line utility that comes with Couchbase.</p><pre>#Copy the two JSON files<br>docker cp  in_network.json  db:/tmp/<br>docker cp  prov_ref.json    db:/tmp/<br>#Connect to the shell of the docker container<br>docker exec -it db /bin/bash<br>root@c085e2fe648d:/#</pre><p>From inside the docker shell we run the following commands to populate the in_network and provider_references collections using <a href="https://docs.couchbase.com/server/current/tools/cbimport-json.html">cbimport</a> command line utility.</p><pre>cbimport json --username  $cb_user  --password $cb_pass \<br>--bucket pt_bucket --scope-collection-exp payer.in_network  \<br>--dataset file:///tmp/in_network.json -f list -c localhost \<br>-g key::%billing_code%::%billing_code_type%::%negotiation_arrangement%</pre><pre>cbimport json --username  $cb_user  --password $cb_pass \<br>--bucket pt_bucket --scope-collection-exp payer.provider_references  \<br>--dataset file:///tmp/prov_ref.json -f list -c localhost \<br>-g key::%provider_group_id% </pre><p>The sample data imported contains 464 documents. That means there are rate information for 464 billing codes.</p><p>Lets run some N1QL queries exploring how much this data can unfold.</p><pre>SELECT COUNT(*) FROM pt_bucket.payer.in_network r<br>-- Returns 464 Original Documents</pre><p>Next we will UNNEST the first level negotiated_rates list which gives us close to 100K items.</p><pre>SELECT COUNT(*) FROM pt_bucket.payer.in_network r<br>UNNEST r.`negotiated_rates` p<br>-- 93 323 items rates unnesting<br></pre><p>Finally we will go one level down by unnesting the last element in the in_network collection.</p><pre>SELECT COUNT(*) FROM pt_bucket.payer.in_network r<br>UNNEST r.`negotiated_rates` p<br>UNNEST p.`provider_references` ppr<br>-- 12 077 103 provider unnesting</pre><p>So from 464 lines we went to 12 Million lines. And we just unnested two of the objects.</p><p>Lets try a simple SQL JOIN between the JSON documents in_network and provider_references</p><pre>SELECT    ppr,t1.billing_code,pg.* <br>FROM  pt_bucket.payer.in_network t1<br>UNNEST t1.`negotiated_rates` p<br>UNNEST p.`provider_references` ppr<br>INNER JOIN pt_bucket.payer.provider_references t2<br>ON t2.provider_group_id=ppr<br>UNNEST t2.provider_groups pg<br>LIMIT 10</pre><p>With the index below the join executed in under 100 ms using the default <a href="https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/createprimaryindex.html">primary index</a> and index on the JOIN term provider_group_id.</p><pre>CREATE PRIMARY INDEX `#primary` ON `pt_bucket`.`payer`.`in_network`<br><br>CREATE INDEX idx_provider_group_id <br>ON  `pt_bucket`.`payer`.`provider_references`(`provider_group_id`)</pre><p>The queries can be executed from DataGrip as well from the web console. One of the advantage of the web console is that we have the plan execution diagram.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*XNhRnS28__ILYqPU0uQXtw.png" /><figcaption>Explain Plan Execution</figcaption></figure><p>This is all great so far, but one might say that it is not a good real-world scenario since all the data is in-memory. Plus the query above is not really a real-world use-case since it is lacking the context on the proivider descriptions. We need to be able to lookup providers based on their attributes such as specialty, zip code, names.</p><p>Next we deploy an actual cluster on AWS on which we would load larger datasets and run some additional queries.</p><h3>Deployment of a Couchbase cluster on AWS with Terraform</h3><p>Simply launch a number of <a href="https://aws.amazon.com/ec2/instance-types/m5/">m5d</a> EC2s on AWS and install Couchbase server via the userdata script. We also deploy a single t2 compute instance with attached large GP3 EBS volume. We will use that to run all the data loading tasks with cbimport cli. The repository that accomplished this can be found here.</p><p><a href="https://github.com/mirkenstein/cb-ec2-terraform-aws">GitHub - mirkenstein/cb-ec2-terraform-aws</a></p><p>The Terraform scripts also assumes that we have existing Route 53 Hosted zones managing a DNS domain that we own.</p><p>A diagram showing the computes deployed and their logical roles.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/960/1*KWVxQa2uXNb5SxUzmUMT0g.png" /></figure><p>Next we initialize the <a href="https://docs.couchbase.com/server/current/cli/cbcli/couchbase-cli-node-init.html">nodes</a> on all 5 instances at once with the Ansible script posted here:</p><p><a href="https://github.com/mirkenstein/cb-ansible-config">GitHub - mirkenstein/cb-ansible-config</a></p><p>After that we set up a new cluster on one of the nodes via the <a href="https://docs.couchbase.com/server/current/manage/manage-nodes/create-cluster.html#set-up-a-new-cluster">web console </a>and joined the rest of the nodes to that cluster with the <a href="https://docs.couchbase.com/server/current/rest-api/rest-cluster-addnodes.html">REST API</a>.</p><pre>curl -kv -X POST -u $cb_user:$cb_pass \<br>https://cb01.example.com:18091/controller/addNode \<br>-d &#39;hostname=cb02.example.com&#39; \<br>-d  user=${cb_user}  \<br>-d  password=${cb_pass}  \<br>-d &#39;services=kv,query&#39;</pre><h3>Dealing with Many Larger files</h3><p>I run my tests on datasets that were cumulatively over 100GB JSON files. Loaded all the files from a given group at once in seconds. The data footprint of the Couchbase bucket is reduced by a factor of 10 for any of the various combinations of MRF files loaded.</p><h4>Preparing large files for the cluster</h4><p>Now that we have a real cluster we can explore larger datasets and some real world use-case scenarios. Downloaded a number of larger MRF JSON files with size that is in the range 10–30 GB and split them with <a href="https://stedolan.github.io/jq/manual/#Streaming">jq stream option</a>. The multiple rates and provider lists were extracted in a separate sub-folders ./cb_import/in_network and ./cb_import/prov_ref/</p><pre>jq -c --stream &#39;fromstream(2|truncate_stream(inputs|select(.[0][0]==&quot;in_network&quot;)))&#39; $1  &gt;./cb_import/in_network/${1%.json}.json<br>jq -c --stream &#39;fromstream(2|truncate_stream(inputs|select(.[0][0]==&quot;provider_references&quot;)))&#39; $1  &gt;./cb_import/prov_ref/${1%.json}_prov_ref.json</pre><p>The commands above take hours to execute but they do finish eventually. I typically run these overnight or right before going away from the computer for a long time and continue from there. The extracted list are one per line.</p><h4>Loading the large files</h4><p>Per the MRF file naming standard</p><p><a href="https://github.com/CMSgov/price-transparency-guide#file-naming-convention">GitHub - CMSgov/price-transparency-guide: The technical implementation guide for the tri-departmental price transparency rule.</a></p><p>the payers use the following name structure<br>&lt;YYYY-MM-DD&gt;_&lt;payer or issuer name&gt;_&lt;plan name&gt;_&lt;file type name&gt;.&lt;file extension&gt;</p><p>for example</p><p>2022-12-01_Some-Payer-Name-Inc_TPA_AB1-10_C3_in-network-rates.json</p><p>We will need to extract the &lt;plan name&gt; string <strong>AB1-10_C3</strong> so that we can use it as part of the key for our items. We will extract that string via GNU grep with PCRE and Capture groups. For the filename example shown above the grep portion of the script would look like this:</p><pre>grep -oP &#39;(?&lt;=Administrator_).+?(?=_in-net)&#39; )</pre><p>From inside of each ./cb_import/ sub-directories we load all the files at once for a given collection.</p><pre>for i in `ls`;do export l=$(echo $i|grep -oP &#39;(?&lt;=Administrator_).+?(?=_in-net)&#39; ); \<br>echo &quot;Loading Rates from file $i&quot;;  \<br>/opt/couchbase/bin/cbimport json --username  $cb_user  --password $cb_password --bucket $bucket \<br>--dataset  file://$i  -f lines \<br>-c $cb_connection_string --no-ssl-verify  \<br>-g key::$l::%billing_code%::%billing_code_type%::%negotiation_arrangement% \<br>--scope-collection-exp payer.in_network -t 4; \<br>done</pre><p>The provider references files were extracted into a separate directory ./cb_import/prov_ref/ From inside that directory we will load the provider_references collection.</p><pre>for i in `ls`;do export l=$(echo $i|grep -oP &#39;(?&lt;=Administrator_).+?(?=_in-net)&#39; ); \<br> echo &quot;Loading Prov Ref from file $i&quot;; ;  \<br>/opt/couchbase/bin/cbimport json --username  $cb_user  --password $cb_password --bucket $bucket \<br>--dataset  file://$i  -f lines \<br>-c $cb_connection_string  --no-ssl-verify  \<br>-g key::$l::%provider_group_id% \<br>--scope-collection-exp payer.provider_references -t 4; \<br>done</pre><p>The collection storage footprint is 10x smaller than that of the source data.</p><p>It takes seconds to load 100GB files and this process is only limited by the network bandwidth and number of CPUs that that we have on the compute from which we load the data.</p><p>The environment variable used in the data loading</p><pre>export cb_connection_string=&quot;https://cb03.example.com:18091,cb02.example.com:18091,cb01.example.com:18091&quot;<br>export cb_user=&lt;User String&gt;<br>export cb_password=&lt;Password String&gt;<br>export bucket=pt_bucket</pre><h4>Adding human readable provider Information from NPPES</h4><p>Before executing the queries we will add another data source that will utilize to enrich our insights with some context information on the provider npi list that is the key provider identifier provider_references.provider_groups .</p><p>We will add the NPPES CMS dataset, whcih is a in a CSV format from here</p><p><a href="https://download.cms.gov/nppes/NPI_Files.html">https://download.cms.gov/nppes/NPI_Files.html</a></p><p>Download the latest Full Replacement Monthly NPI File and extract the zip archive. The extracted file will be over 8GB size and will contains millions of entries. We will use the cbimport-csv without the --infer-types argument so that all attributes will be strings.</p><pre>/opt/couchbase/bin/cbimport csv   -c  $cb_connection_string  --no-ssl-verify   \<br>-u $cb_user -p  $cb_password  \<br>-d &#39;file://npidata_pfile_20050523-20221211.csv&#39; -b &#39;pt_bucket&#39; \<br>--scope-collection-exp &quot;provider.nppes&quot; -g %NPI% -t 4</pre><p>This files took over 10 minute to load and the collection storage size is very similar to the size of the CSV file.</p><h3><strong>N1QL in action to the fullest</strong></h3><p>Here is one query which is more representative to a real-world usecase defined by the quote.</p><blockquote>“…provide additional access to pricing information and enhance consumers’ ability to shop for the health care that best meet their needs”</blockquote><p>which translates to somthing along the lines… For my insurance plan find all the providers and their rates for a medical procedure that I am interested in a given geographic area.</p><p>The filter criteria for this scenario would be</p><ol><li>Billing Code</li><li>Provider Zip Code</li><li>Plan ID which is contained in the sub-string from the filename from which the rates were uploaded from.</li></ol><p>Example query that executes in seconds:</p><pre>SELECT  t.billing_code, pr.negotiated_rate,npi,SUBSTR(nppes.`Provider Business Practice Location Address Postal Code`,0,5)<br>FROM `pt_bucket`.`payer`.`in_network` t<br>UNNEST t.`negotiated_rates` p<br>UNNEST p.`provider_references` ppr<br>UNNEST p.`negotiated_prices` pr<br>INNER JOIN `pt_bucket`.`payer`.provider_references t2 ON t2.provider_group_id = ppr<br>UNNEST t2.provider_groups pg<br>UNNEST pg.npi npi<br>INNER JOIN pt_bucket.provider.nppes nppes  ON  to_number(nppes.NPI) = npi<br>LET BILLING_CODE=&quot;J0129&quot;, POST_CODE=&quot;56751&quot;, FILE_ID=&quot;AB1-10_C3&quot;<br>WHERE <br>        t.billing_code = BILLING_CODE<br>    AND SUBSTR(nppes.`Provider Business Practice Location Address Postal Code`,0,5)=POST_CODE<br>    AND SPLIT(Meta(t).id,&quot;::&quot;)[1] = FILE_ID<br>LIMIT 10;</pre><p>With these indices</p><pre>CREATE INDEX idx_provider_group_id ON <br>`default`:`pt_bucket`.`payer`.`provider_references`(<br>`provider_group_id`<br>)<br><br>CREATE INDEX idx_in_network_provider_references ON <br>`pt_bucket`.`payer`.`in_network`<br>(`billing_code`,<br>    (distinct (array<br>        (distinct (array `y` for `y` in (`x`.`provider_references`) end<br>         ))<br>      for `x` in `negotiated_rates` end<br>    ))<br>);<br><br>CREATE INDEX idx_to_number_NPI_substr_ProviPostCode05 ON <br>`pt_bucket`.`provider`.`nppes`(to_number(`NPI`),<br> substr0(`Provider Business Practice Location Address Postal Code`, 0, 5)) </pre><p>Adding the <a href="https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/keyspace-hints.html#use_hash">keyspace hint</a>USE HASH(build) together with another set of indices the query execution is lowered to under a second.</p><pre>CREATE INDEX idx_inNetwork_billingCd_zip ON <br>`default`:`pt_bucket`.`payer`.`in_network`(<br>          `billing_code`,<br>          (split((meta().`id`), &#39;::&#39;)[1]),<br>          substr0((`nppes`.`Provider Business Practice Location Address Postal Code`), 0, 5)) <br>PARTITION BY HASH(<br>  `billing_code`,(split((meta().`id`), &#39;::&#39;)[1]),<br>   substr0((`nppes`.`Provider Business Practice Location Address Postal Code`), 0, 5)<br>)<br><br>CREATE INDEX idx_provider_group_id ON <br>`default`:`pt_bucket`.`payer`.`provider_references`(<br>`provider_group_id`<br>)<br><br>CREATE INDEX idx_nppes_npi_postal_code ON <br>`default`:`pt_bucket`.`provider`.`nppes`(<br>to_number(`NPI`),<br>substr0(`Provider B  usiness Practice Location Address Postal Code`, 0, 5))<br>PARTITION BY HASH(<br>  substr0(`Provider Business Practice Location Address Postal Code`, 0, 5)<br>)</pre><p>and the inner join clause is now</p><pre><br>INNER JOIN pt_bucket.provider.nppes nppes USE HASH(build) ON to_number(nppes.NPI) = npi<br></pre><h3>Couchbase Analytics Service</h3><p>Another product I tried is the Couchbase Analytics Service.</p><p><a href="https://docs.couchbase.com/server/current/learn/services-and-indexes/services/analytics-service.html">Analytics Service</a></p><p>There the queries took longer to execute without the creation of any indices whatsover. This seems to be good for general operational analytics. It also supports indices but the syntax there is different and I did not get much time to play with that side of the product.</p><h3>Concluding Remarks</h3><p>Looks like we can eat the cake and have it at the same time. Otherwise said we can have ANSI SQL JOINS on a complex nested JSON documents.</p><p>In a follow up in a separate post with detailed benchmarks on even larger MRF datasets and more queries.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=758cc9336778" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>