0

I am using Firebase to store my data. I have an output from Firebase in JSON format-->

https://fundeasy-b6187.firebaseio.com/Test.json

{"iID":15,"vValue":"K"}

I want to select it using an SQLite command. SQLite has json_extract and json_set functionalities. Can these be used to select data directly from my JSON output?

Improve this question
5
  • You want to select??? What? Commented Oct 7, 2017 at 20:03
  • Hello, My JSON output ( fundeasy-b6187.firebaseio.com/Test.json ) has two parameters iID and vValue. Their values will change everyday. I want to select the current iID and vValue parameters from JSON using SQLite query. Commented Oct 8, 2017 at 21:27
  • Why dont you just use JSON itself from Java Default classes called JSONObject, JSONarray etc. Why sqlite? Commented Oct 9, 2017 at 4:20
  • I am using a third party service to develop my Android app. I don't have access to most of the code here. I can only define SQLite APIs with custom code. Hence, I was wondering if there is a way to achieve it in SQLite. Commented Oct 9, 2017 at 7:18
  • I didnt know if there is a json extension in sqlite so i go and check the documentation and I have written you an answer! Scroll to see! Commented Oct 9, 2017 at 8:11

2 Answers 2

Reset to default
0

This is how you do according to the official SQLite documentation on JSON. The way to extract a certain value from a JSON Object and for your case the json object is:

{"iID":15,"vValue":"K"}

When selecting first you need to know the keys of your json object so as to get the values. For your case the keys are iID and another key is vValue. Remember they are case sensitive.

To get the values from your json object we use the sqlite function called json_extract function whose syntax is as follows for your case for :

json_extract('your json object here', '$.key_of_the_value')

Hence for your case it will be:

For iID

json_extract('your_json_object_here', '$.iID')

And for vValue

json_extract('your_json_object_here', '$.vValue')

Hence to summarize everything for your case the solution will be:

SELECT JSON_EXTRACT('your_json_object_here', '$.iID');
SELECT JSON_EXTRACT('your_json_object_here', '$.vValue');

For more information for the extract function and json extension for sqlite like extracting json arrays or items in an json array check this link. Happy Coding!

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

3 Comments

Thank you so much in explaining it in depth. This is really helpful. I will try out the solution.
Give it an upvote and accept it as an answer to your question in the left side!
Hello, I have accepted it as the answer to my question. I am unable to upvote as I don't have enough reputation points. Thank you so much for your help :)
0

Yes they can. suppose you need to extract iID you can use the following code

' SELECT JSON_EXTRACT('{"iID":15,"vValue":"K"}', 'YOUR VARIABLE');'

See the following link for more customization

Improve this answer

1 Comment

Hello Vidya, Thanks a lot for your answer. In this case my JSON output will change everyday i.e. fundeasy-b6187.firebaseio.com/Test.json will have different values of iID and vValue. How can the query be defined so it takes variable values of iID and vValue from my JSON output ( fundeasy-b6187.firebaseio.com/Test.json )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.