How to modify a JSON to a particular SQL query format in javascript?

Question

I have a requirement to modify a JSON to a JSON having the values in a SQL query format. To better explain.

{
    "Assets": {
        "ASSOCIATES_NAME": ["David", "Philip"],
        "CAR_NAME": ["Verron"]
    }
  , "Product":{"SELLER_NAME": ["XXXXX"]}
}

The result should be having the json values as an sql query leaving the keys as it is. So the resulting query will be:

{
  Assets: "(ASSOCIATES_NAME = 'David' OR ASSOCIATES_NAME = 'Philip') AND CAR_NAME = 'Verron'", 
  Product: "SELLER_NAME = 'XXXXX'"
}

I tried something but I couldn't figure it out well. Below it is:

console.log(Object.entries(a).map(x => {return {
  [x[0]]: `${Object.keys(x[1])} = '${Object.values(x[1])}'`,
  }}))

However I still need to figure out how to group individual values of an array. Any elegant ES6 based solution to this?. Please folks help me out on this. TIA

Consider parsing the JSON string? JSON.parse. Also, I'm not sure why you would need regex in this situation. — evolutionxbox
– evolutionxbox, Commented Dec 23, 2020 at 11:11
Yes I parsed the string. however the problem lies with the values.... I tried numerous ways like Object.entries, values etc. — Bratty Neal
– Bratty Neal, Commented Dec 23, 2020 at 11:13
Please may you share those previous tries. Making a simple minimal reproducible example of each try is best. — evolutionxbox
– evolutionxbox, Commented Dec 23, 2020 at 11:14
Just a warning: Please be very careful of SQL Injection attacks when handling raw SQL queries in the frontend. — Shell Code
– Shell Code, Commented Dec 23, 2020 at 11:19

Nick · Accepted Answer · 2020-12-23 11:25:50Z

3

Here's one way to do it, using Array.reduce to iterate over the outer object properties and Array.map over the inner object properties to generate the OR and AND expressions:

const json = `{
    "Assets": {
        "ASSOCIATES_NAME": ["David", "Philip"],
        "CAR_NAME": ["Verron"]
    }
  , "Product":{"SELLER_NAME": ["XXXXX"]}
}`
const params = JSON.parse(json);

const whereItems = Object.entries(params)
  .reduce((c, [k, v]) => {
    c[k] = Object.entries(v)
      .map(([param, value]) => '(' + value.map(v => param + " = '" + v + "'").join(' OR ') + ')')
      .join(' AND ');
    return c;
  }, {});
console.log(whereItems);

Note that it does produce unnecessary () around the single comparisons (e.g. (SELLER_NAME = 'XXXXX')), if you really want to get rid of them just condition the addition of those characters on value.length > 1.

edited Dec 23, 2020 at 11:25

answered Dec 23, 2020 at 11:23

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

evolutionxbox Over a year ago

(You could use a template string to avoid the backslashes?)

Nick Over a year ago

@evolutionxbox indeed. that's a lot cleaner; I've edited it in. thanks.

Bratty Neal Over a year ago

Oh good heavens. Thanks a lot @Nick. You saved my day. Just made a minor change as suggested by @evolutionxbox ${value.map(v => param + " = '" + v + "'").join(" OR ")}

Bratty Neal Over a year ago

Yeah.... @Nick what are your thoughts when the value is not array but just a string if in case of a single value. like ["Verron"] would be just "Verron"

Nick Over a year ago

You'd need to edit the code that dealt with value to check if it was a string or an array, possibly simplest to just turn it into an array if it's not.

dileep nandanam · Accepted Answer · 2020-12-23 12:25:47Z

1

upvote for @nik's answer, here is just another way for beginners.

f3 = function(key, arr){
var result = arr.map(function(e) {
  return(`${key}='${e}'`)
})
if(result.length > 1)
  return(`(${result.join(' AND ')})`)
else
  return(result)
}

f2 = function(json) {
  var result = []
  for(const key in json) {
    result.push(f3(key, json[key]))
  }
  return(result.join(' OR '))
}

f = function(json) {
  var result = {}
  for(const key in json) {
    result[key] = f2(json[key])
  }
  return(result)
}

call f over your json

answered Dec 23, 2020 at 12:25

dileep nandanam

2,91520 silver badges21 bronze badges

Collectives™ on Stack Overflow

How to modify a JSON to a particular SQL query format in javascript?

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related