Loading ...
Sorry, an error occurred while loading the content.

Expressing SQL queries in JSON

Expand Messages
  • Guillaume Filion
    Hi, I m developing a web front-end to our student database and I d like to let the users specify what fields they want in their view and be able to filter and
    Message 1 of 4 , Apr 29 12:21 PM
    • 0 Attachment
      Hi,

      I'm developing a web front-end to our student database and I'd like to
      let the users specify what fields they want in their view and be able to
      filter and sort the data.

      After some thinking I figured out that this all can be expressed by an
      SQL query -- and it will end up generating an SQL query on the server.

      So I made a JSON syntax like this:
      {
      "fields":["std_id","code","lastname","firstname"],
      "filters":[{"prog":["200A0","200A1"]},{"decision":["A%"]}],
      "sort":[{"cote_mix":"desc"}]
      }
      which would generate an SQL query like this:
      SELECT std_id, code, lastname, firstname FROM students
      WHERE prog IN ('200A0','200A1') AND decision LIKE 'A%'
      ORDER BY cote_mix DESC

      But I feel like I'm re-inventing the wheel here...
      Is there already a specification for expressing an SQL query in JSON?

      Thanks,
      GFK's
      --
      Guillaume Filion
      http://guillaume.filion.org/



      [Non-text portions of this message have been removed]
    • Gene Berger
      I a new at JSON but have been doing stuff like this for 27 years. (JSON is just another form of data transportation as when EDI was king over XML 10-25 years
      Message 2 of 4 , May 4, 2009
      • 0 Attachment
        I a new at JSON but have been doing stuff like this for 27 years. (JSON is
        just another form of data transportation as when EDI was king over XML 10-25
        years ago.)



        You should only give your user's the options on a screen of what fields to
        choose, not have them write out the fields names themselves. (Is that what
        you are doing?) You would face a big inject attack if you let them write
        the SQL fields themselves.



        Gene











        From: json@yahoogroups.com [mailto:json@yahoogroups.com] On Behalf Of
        Guillaume Filion
        Sent: Wednesday, April 29, 2009 3:21 PM
        To: json@yahoogroups.com
        Subject: [json] Expressing SQL queries in JSON








        Hi,

        I'm developing a web front-end to our student database and I'd like to
        let the users specify what fields they want in their view and be able to
        filter and sort the data.

        After some thinking I figured out that this all can be expressed by an
        SQL query -- and it will end up generating an SQL query on the server.

        So I made a JSON syntax like this:
        {
        "fields":["std_id","code","lastname","firstname"],
        "filters":[{"prog":["200A0","200A1"]},{"decision":["A%"]}],
        "sort":[{"cote_mix":"desc"}]
        }
        which would generate an SQL query like this:
        SELECT std_id, code, lastname, firstname FROM students
        WHERE prog IN ('200A0','200A1') AND decision LIKE 'A%'
        ORDER BY cote_mix DESC

        But I feel like I'm re-inventing the wheel here...
        Is there already a specification for expressing an SQL query in JSON?

        Thanks,
        GFK's
        --
        Guillaume Filion
        http://guillaume.filion.org/

        [Non-text portions of this message have been removed]





        [Non-text portions of this message have been removed]
      • Guillaume Filion
        ... Yes the user would have a nice graphical interface to specify which fields he wants. Also, I would validate the fields names on the backend before creating
        Message 3 of 4 , May 6, 2009
        • 0 Attachment
          Gene Berger a écrit :
          > You should only give your user's the options on a screen of what fields to
          > choose, not have them write out the fields names themselves. (Is that what
          > you are doing?) You would face a big inject attack if you let them write
          > the SQL fields themselves.

          Yes the user would have a nice graphical interface to specify which
          fields he wants. Also, I would validate the fields names on the backend
          before creating the SQL query.

          Cheers,
          GFK's
          --
          Guillaume Filion
          http://guillaume.filion.org/



          [Non-text portions of this message have been removed]
        • nic.volanschi
          ... The most similar technology is LINQ, which for JavaScript translates as JSLINQ (http://jslinq.codeplex.com/). But you may take a look to
          Message 4 of 4 , May 11, 2009
          • 0 Attachment
            --- In json@yahoogroups.com, Guillaume Filion <gfk@...> wrote:
            > which would generate an SQL query like this:
            > SELECT std_id, code, lastname, firstname FROM students
            > WHERE prog IN ('200A0','200A1') AND decision LIKE 'A%'
            > ORDER BY cote_mix DESC
            >
            > But I feel like I'm re-inventing the wheel here...
            > Is there already a specification for expressing an SQL query in JSON?

            The most similar technology is LINQ, which for JavaScript translates as JSLINQ (http://jslinq.codeplex.com/).

            But you may take a look to myPatterns.free.fr, too, which complements LINQ with JSON patterns.

            Regards,
            Nic.
          Your message has been successfully submitted and would be delivered to recipients shortly.