locked
Can you directly query the db where there is JSON data using Linq? RRS feed

  • Question

  • User1901201124 posted

    Been searching around but haven't been able to come up with a good solution for this.

    But in one of my db tables, say Table1, I store JSON data in a field.

    What I need to be able to do is count how many records have the StatusId of 1.

    Is there a Linq query that would allow me to directly query the database and parse the rows which have the JSON data?

    Say Table1 has the following rows:

    {"Name":"Bob","JobTitle":"Teacher","StatusId":1}
    {"Name":"Jim","JobTitle":"Janitor","StatusId":0}
    {"Name":"Shaun","JobTitle":"Accountant","StatusId":1}

    Thanks for helping out!

    Friday, March 15, 2019 2:32 PM

Answers

  • User-474980206 posted
    Not directly. You can either read the whole table into a list, then in deserialize each row, and count via linq to objects, or use a native sql query via raw sql query support.
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, March 15, 2019 3:12 PM

All replies

  • User-474980206 posted
    Not directly. You can either read the whole table into a list, then in deserialize each row, and count via linq to objects, or use a native sql query via raw sql query support.
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, March 15, 2019 3:12 PM
  • User1901201124 posted

    ah man that sucks. I guess since it is a string i'll just use Contains and do a count that way.

    Friday, March 15, 2019 4:12 PM
  • User753101303 posted

    Hi,

    You have what you asked for. Using contains could not always work. I would use https://docs.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql?view=sql-server-2017

    Another option could be to still store JSON but have computed column(s) to expose the "raw" values you need for easy consumption. Make sure using JSON is what you need most of the time...

    Friday, March 15, 2019 4:42 PM
  • User-474980206 posted

    ah man that sucks. I guess since it is a string i'll just use Contains and do a count that way.

    its a simple sql query. say the field is named jsonData, the query is:

    select count(*) as count 
    from table1
    where json_value(jsonData, '$.StatusId') = 1
    


     

    Friday, March 15, 2019 5:53 PM
  • User1901201124 posted

    I tried doing that but it keeps returning -1 as the count.

    Friday, March 15, 2019 6:45 PM
  • User-474980206 posted
    Then you’re calling the sql code is incorrectly. The query can only return >= 0
    Friday, March 15, 2019 10:35 PM