1

I have JSON document. structure/sample data is like -

{
   "id":"201",
   "portfolio":[
      {
         "portfolio_id":"PORTFOLIO_001",
         "portfolio_name":"AAA",
         "product":[
            {
               "product_id":"PORTFOLIO_001_PRODUCT_001",
               "product_trigram":"PORTFOLIO_001_PRODUCT_001_1",
               "product_name":"PORTFOLIO_001_PRODUCT_001_NAME"
            },
            {
               "product_id":"PORTFOLIO_001_PRODUCT_002",
               "product_trigram":"PORTFOLIO_001_PRODUCT_002_1",
               "product_name":"PORTFOLIO_001_PRODUCT_002_NAME"
            }
         ]
      },
      {
         "portfolio_id":"PORTFOLIO_002",
         "portfolio_name":"BBB",
         "product":[
            {
               "product_id":"PORTFOLIO_002_PRODUCT_001",
               "product_trigram":"PORTFOLIO_002_PRODUCT_001_1",
               "product_name":"PORTFOLIO_002_PRODUCT_001_NAME"
            }
         ]
      }
   ]
}

I have written select SQL as below. I want to fetch products of that specific portfolio(PORTFOLIO_001) and id. I am getting all products of all portfolios instead of products of specific portfolio(PORTFOLIO_001)

SELECT json_extract(j, '$.portfolio[*].product') FROM t WHERE json_contains(json_extract(j, '$.portfolio[*].portfolio_id'), '"PORTFOLIO_001"') AND JSON_CONTAINS(j, '"201"', '$.id')

Actual output -

[
   [
      {
         "product_id":"PORTFOLIO_001_PRODUCT_001",
         "product_trigram":"PORTFOLIO_001_PRODUCT_001_1",
         "product_name":"PORTFOLIO_001_PRODUCT_001_NAME"
      },
      {
         "product_id":"PORTFOLIO_001_PRODUCT_002",
         "product_trigram":"PORTFOLIO_001_PRODUCT_002_1",
         "product_name":"PORTFOLIO_001_PRODUCT_002_NAME"
      }
   ],
   [
      {
         "product_id":"PORTFOLIO_002_PRODUCT_001",
         "product_trigram":"PORTFOLIO_002_PRODUCT_001_1",
         "product_name":"PORTFOLIO_002_PRODUCT_001_NAME"
      }
   ]
]

Expected output -

[
    {
     "product_id":"PORTFOLIO_001_PRODUCT_001",
     "product_trigram":"PORTFOLIO_001_PRODUCT_001_1",
     "product_name":"PORTFOLIO_001_PRODUCT_001_NAME"
    },
    {
     "product_id":"PORTFOLIO_001_PRODUCT_002",
     "product_trigram":"PORTFOLIO_001_PRODUCT_002_1",
     "product_name":"PORTFOLIO_001_PRODUCT_002_NAME"
    }
]

Can someone please help me to resolve this issue ?

Thanks in advance for your action :-)

Regards, Prashant

Prashant Vidhate
  • 11
  • 1
  • `'$.portfolio[0].product'` ? – Akina May 14 '21 at 10:25
  • @Akina, If I put in such a way then it will always returns the 1st element from array. In that case if I filter on PORTFOLIO_002 then also it will return me the products of PORTFOLIO_001 – Prashant Vidhate May 14 '21 at 10:35
  • If so then use nested extraction (subquery or CTE). Firstly extract $.portfolio from the object where $.id = 201, then extract $.product from the object where $.portfolio_id = 'PORTFOLIO_001' – Akina May 14 '21 at 10:56
  • It returns the array. Same problem for index as we are getting in old(first) SQL – Prashant Vidhate May 18 '21 at 11:01

0 Answers0