title: Postgresql JSON Sql coding
body: <p>How to write JSON SQL coding for the below json data:</p>
  <pre><code>{
     &quot;orderSystem&quot;:&quot;SAP&quot;,
     &quot;orderHeader&quot;:{
        &quot;salesDoc&quot;:&quot;0011214280&quot;,
        &quot;soldTo&quot;:&quot;0000089890&quot;,
        &quot;createDate&quot;:&quot;2020-01-02&quot;,
        &quot;purchNo&quot;:&quot;2020-00-16213&quot;,
        &quot;billingStatus&quot;:&quot;billed&quot;,
        &quot;ecomStatus&quot;:&quot;Complete/Invoiced&quot;,
        &quot;purchDate&quot;:&quot;2020-01-02&quot;,
        &quot;netValHd&quot;:&quot;33416.500000000&quot;,
        &quot;taxesHd&quot;:&quot;0.000000000&quot;,
        &quot;currency&quot;:&quot;USD&quot;
     },
     &quot;orderItems&quot;:[
        {
           &quot;docNumber&quot;:&quot;0011214280&quot;,
           &quot;itmNumber&quot;:&quot;000100&quot;,
           &quot;netValue&quot;:&quot;33416.500000000&quot;,
           &quot;currency&quot;:&quot;USD&quot;,
           &quot;shipments&quot;:[
              {
                 &quot;docNumber&quot;:&quot;0011214280&quot;,
                 &quot;itmNumber&quot;:&quot;000100&quot;,
                 &quot;scheduleLine&quot;:&quot;0001&quot;,
                 &quot;material&quot;:&quot;000000000016121362&quot;
              }
           ]
        }
     ],
     &quot;invoices&quot;:[
        {
           &quot;billingDoc&quot;:&quot;0048471944&quot;,
           &quot;docCategory&quot;:&quot;M&quot;,
           &quot;currency&quot;:&quot;USD&quot;,
           &quot;status&quot;:&quot;CLOSED&quot;,
           &quot;invoiceUrl&quot;:&quot;/ZSD_BILLING_API_GET_PDF&quot;
        }
     ]
  }
  </code></pre>
  <p>Here &quot;orderSystem&quot;:&quot;SAP&quot; is hard coded value. Then all &quot;orderHeader&quot; tags are from sales_order table. &quot;orderItems&quot; tags from sales_order_item table. &quot;shipments&quot; are from sales_order_shipment table. &quot;invoices&quot; are from invoice table.</p>
  <p>sales_order table have single record for an order.
  sales_order_item table may have multiple records for an order.
  sales_order_shipment table may have multiple records for an order.
  invoices table may have multiple records for an order.</p>
  <p>Here joining column for all table is &quot;salesDoc&quot; column. How to achieve it sql json query?
  Example i am giving input as salesDoc data. Then i need to pull the data in the above json format.</p>