CODEWITHSUNDEEP
mysqlmysql-json
Knommy Bassy
Knommy Bassy

Reputation: 1

get the position of json in mysqli query

Database Column:
inquiry.productdescription=[
  {"productkey":"yrty","productvalue":"700*600","quantity":"5","note":"round"},
  {"productkey":"c","productvalue":"600*4000","quantity":"1000","note":"notess"},
  {"productkey":"rtyrty","productvalue":"600*900","quantity":"3","note":"tertert"}
]

providerinqlog.price_json=["70","30","60"]

providerinqlog.time_json=["3 mons","200","4 week"]

suppose in above example, '{"productkey":"c", "productvalue":"600*4000"}' matches with 1st position of inquiry.productdescription so get the data of 1st position of providerinqlog.price_json and providerinqlog.time_json like here I should get 30 in price_json and 200 in time_json

SELECT * FROM inquiry 
JOIN providerinqlog ON providerinqlog.inquiry_id =inquiry.id 
WHERE JSON_CONTAINS(inquiry.productdescription,
   '{"productkey":"c", "productvalue":"600*4000"}') 
 AND inquiry.userid=128 
 and inquiry.id!=320 
 and providerinqlog.provider_id=159 
 and (providerinqlog.status_inq=2 OR providerinqlog.status_inq=3);

I tried this query but i am getting all data with price_json as ["70","30","60"] and time_json as ["3 mons","200","4 week"]

Upvotes: -1

Views: 51

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562871

Here's a solution, tested on MySQL 8.0 or later.

select j.productkey, j.productvalue, 
  json_unquote(json_extract(providerinqlog.price_json, concat('$[', j.rownum, ']'))) as price,
  json_unquote(json_extract(providerinqlog.time_json, concat('$[', j.rownum, ']'))) as time
from inquiry
cross join json_table(inquiry.productdescription, '$[*]' columns (
    productkey varchar(10) path '$.productkey',
    productvalue varchar(10) path '$.productvalue',
    quantity int path '$.quantity',
    note text path '$.note',
    rownum for ordinality
  )) as j
cross join providerinqlog
where j.productkey = 'c' and j.productvalue = '600*4000'
 AND inquiry.userid=128
 and inquiry.id!=320
 and providerinqlog.provider_id=159
 and (providerinqlog.status_inq=2 OR providerinqlog.status_inq=3);

Demo: https://dbfiddle.uk/Z77qGoRD

This is made more complicated than it should be because you store data in JSON instead of normal rows and columns. If you used a normal relational database design, it should look something like this:

select p.productkey, p.productvalue, l.price, l.time
from inquiry_product as p
join providerinqlog as l on p.id = l.inquiry_product_id
where p.productkey = 'c' and p.productvalue = '600*4000'
  and l.provider_id = 159 and l.status_inq in (2, 3);

This does not use JSON. Each array item in your inqiry table should be a separate row in a second table which I have called inquiry_product in this example. Each field of the JSON object should be in its own column.

There is no reason given your example that you should be using JSON. It doesn't save anything, in fact it uses more storage. It makes queries more complicated and difficult.

Upvotes: 1

Related Questions