Convert json query to insert a variable and re-convert it to json query

Question

I am kinda frustrated. I copied the following Metabase query string from the network tab in the browser:

query = "{\"database\":17,\"query\":{\"source-table\":963,\"filter\":[\"and\",[\"=\",[\"field\",17580,null],\"XXXXXX_XXXXXX\"],[\"=\",[\"field\",17599,null],\"**chl-43d813dd-05a7-45b8-a5b0-8eb960289aa5**\"]],\"fields\":[[\"field\",17579,null],[\"field\",17569,null],[\"field\",17572,null],[\"field\",17586,null],[\"field\",17592,{\"temporal-unit\":\"default\"}],[\"field\",17611,null],[\"field\",17582,null],[\"field\",17597,null],[\"field\",17603,null],[\"field\",17607,null],[\"field\",17576,null],[\"field\",17588,null],[\"field\",17596,null],[\"field\",17608,null],[\"field\",17587,{\"temporal-unit\":\"default\"}],[\"field\",17578,{\"temporal-unit\":\"default\"}],[\"field\",17602,null],[\"field\",17606,null],[\"field\",17605,{\"temporal-unit\":\"default\"}],[\"field\",17601,null],[\"field\",17590,null],[\"field\",17580,null],[\"field\",17598,{\"temporal-unit\":\"default\"}],[\"field\",17577,null],[\"field\",164910,null],[\"field\",46951,null],[\"field\",46952,{\"temporal-unit\":\"default\"}]]},\"type\":\"query\",\"middleware\":{\"js-int-to-string?\":true,\"add-default-userland-constraints?\":true}}"

As the next step I wanted to convert it to a String to replace the bold reference with a variable. The String looks like this:

query = '{"database\":17,\"query\":{\"source-table\":963,\"filter\":[\"and\",[\"=\",[\"field\",17580,null],\"XXXXXXXX-XXXXXXXX\"],[\"=\",[\"field\",17599,null],\"'+channelRef+'\"]],\"fields\":[[\"field\",17579,null],[\"field\",17569,null],[\"field\",17572,null],[\"field\",17586,null],[\"field\",17592,{\"temporal-unit\":\"default\"}],[\"field\",17611,null],[\"field\",17582,null],[\"field\",17597,null],[\"field\",17603,null],[\"field\",17607,null],[\"field\",17576,null],[\"field\",17588,null],[\"field\",17596,null],[\"field\",17608,null],[\"field\",17587,{\"temporal-unit\":\"default\"}],[\"field\",17578,{\"temporal-unit\":\"default\"}],[\"field\",17602,null],[\"field\",17606,null],[\"field\",17605,{\"temporal-unit\":\"default\"}],[\"field\",17601,null],[\"field\",17590,null],[\"field\",17580,null],[\"field\",17598,{\"temporal-unit\":\"default\"}],[\"field\",17577,null],[\"field\",164910,null],[\"field\",46951,null],[\"field\",46952,{\"temporal-unit\":\"default\"}]]},\"type\":\"query\",\"middleware\":{\"js-int-to-string?\":true,\"add-default-userland-constraints?\":true}}'

With

 q = json.dumps(query)

the result looks exactly as I want to:

q = "{\"database\":17,\"query\":{\"source-table\":963,\"filter\":[\"and\",[\"=\",[\"field\",17580,null],\"XXXXXXXX-XXXXXXXX\"],[\"=\",[\"field\",17599,null],\"**chl-caabef81-f081-4532-9b6e-ac20b3d4c6cf**\"]],\"fields\":[[\"field\",17579,null],[\"field\",17569,null],[\"field\",17572,null],[\"field\",17586,null],[\"field\",17592,{\"temporal-unit\":\"default\"}],[\"field\",17611,null],[\"field\",17582,null],[\"field\",17597,null],[\"field\",17603,null],[\"field\",17607,null],[\"field\",17576,null],[\"field\",17588,null],[\"field\",17596,null],[\"field\",17608,null],[\"field\",17587,{\"temporal-unit\":\"default\"}],[\"field\",17578,{\"temporal-unit\":\"default\"}],[\"field\",17602,null],[\"field\",17606,null],[\"field\",17605,{\"temporal-unit\":\"default\"}],[\"field\",17601,null],[\"field\",17590,null],[\"field\",17580,null],[\"field\",17598,{\"temporal-unit\":\"default\"}],[\"field\",17577,null],[\"field\",164910,null],[\"field\",46951,null],[\"field\",46952,{\"temporal-unit\":\"default\"}]]},\"type\":\"query\",\"middleware\":{\"js-int-to-string?\":true,\"add-default-userland-constraints?\":true}}"

But when I use this query string to send an API request, I get the following error message(s):

{"via":[{"type":"java.lang.ClassCastException"}],"trace":[],"message":null}

Traceback (most recent call last): File "c:\Users\XXXX\Documents\XXXXXXXX\Test.py", line 308, in main() File "c:\Users\XXXX\Documents\XXXXXXXX\Test.py", line 114, in main some_function(XXXX, window, selected_path) File "c:\Users\XXXX\Documents\XXXXXXXX\Test.py", line 290, in some_function dataframe = DataFrame(result) File "C:\Users\XXXX\AppData\Roaming\Python\Python310\site-packages\pandas\core\frame.py", line 756, in init raise ValueError("DataFrame constructor not properly called!") ValueError: DataFrame constructor not properly called!

Does have anyone have an idea?

Thank you very much in advance!

Robin Dillen · Accepted Answer · 2022-08-29 18:43:12Z

2

You can use the built-in json module:

import json
query = "{\"database\":17,\"query\":{\"source-table\":963,\"filter\":[\"and\",[\"=\",[\"field\",17580,null],\"XXXXXX_XXXXXX\"],[\"=\",[\"field\",17599,null],\"**chl-43d813dd-05a7-45b8-a5b0-8eb960289aa5**\"]],\"fields\":[[\"field\",17579,null],[\"field\",17569,null],[\"field\",17572,null],[\"field\",17586,null],[\"field\",17592,{\"temporal-unit\":\"default\"}],[\"field\",17611,null],[\"field\",17582,null],[\"field\",17597,null],[\"field\",17603,null],[\"field\",17607,null],[\"field\",17576,null],[\"field\",17588,null],[\"field\",17596,null],[\"field\",17608,null],[\"field\",17587,{\"temporal-unit\":\"default\"}],[\"field\",17578,{\"temporal-unit\":\"default\"}],[\"field\",17602,null],[\"field\",17606,null],[\"field\",17605,{\"temporal-unit\":\"default\"}],[\"field\",17601,null],[\"field\",17590,null],[\"field\",17580,null],[\"field\",17598,{\"temporal-unit\":\"default\"}],[\"field\",17577,null],[\"field\",164910,null],[\"field\",46951,null],[\"field\",46952,{\"temporal-unit\":\"default\"}]]},\"type\":\"query\",\"middleware\":{\"js-int-to-string?\":true,\"add-default-userland-constraints?\":true}}"
my_json = json.loads(query)
# make edit's (works like a dict)
query = json.dumps(my_json)

answered Aug 29, 2022 at 18:43

Robin Dillen

7125 silver badges11 bronze badges

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

1 Comment

ALL Over a year ago

Ok, now I am a step further.. Thank you very much!!!

vomitHatSteve · Accepted Answer · 2022-08-29 18:44:19Z

0

I don't see a bold reference in your JSON string, but this is all handled with the json library:

import json
query = "YOUR QUERY STRING"
object = json.dumps(query)
# Make your changes to your dict object here
new_query = json.loads(object)

answered Aug 29, 2022 at 18:44

vomitHatSteve

861 silver badge10 bronze badges

3 Comments

Robin Dillen Over a year ago

You swapped dumps and loads. And you used a reserved keyword(object).

vomitHatSteve Over a year ago

Ah, you're right. Whoops!

ALL Over a year ago

Sorry, the bold part is only marked with ** for some reasons :-/

Collectives™ on Stack Overflow

Convert json query to insert a variable and re-convert it to json query

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related