Skip to content
Advertisement

Extract JSON content in Metabase SQL query

Using: Django==2.2.24, Python=3.6, PostgreSQL is underlying DB
Working with Django ORM, I can easily make all sort of queries, but I started using Metabase, and my SQL might be a bit rusty.

The problem:
I am trying to get a count of the items in a list, under a key in a dictionary, stored as a JSONField:

from django.db import models
from jsonfield import JSONField

class MyTable(models.Model):
  data_field = JSONField(blank=True, default=dict)

Example of the dictionary stored in data_field:

{..., "my_list": [{}, {}, ...], ...}

Under "my_list" key, the value stored is a list, which contains a number of other dictionaries.
In Metabase, I am trying to get a count for the number of dictionaries in the list, but even more basic things, none of which work.

Some stuff I tried:
Attempt:

SELECT COUNT(elem->'my_list') as my_list_count
FROM my_table, json_object_keys(data_field:json) AS elem

Error:

ERROR: syntax error at or near ":" Position: 226

Attempt:

SELECT ARRAY_LENGTH(elem->'my_list') as my_list_count
FROM my_table, JSON_OBJECT_KEYS(data_field:json) AS elem

Error:

ERROR: syntax error at or near ":" Position: 233

Attempt:

SELECT JSON_ARRAY_LENGTH(data_field->'my_list'::json)
FROM my_table

Error:

ERROR: invalid input syntax for type json Detail: Token "my_list" is invalid. Position: 162 Where: JSON data, line 1: my_list

Attempt:

SELECT ARRAY_LENGTH(JSON_QUERY_ARRAY(data_field, '$.my_list'))
FROM my_table

Error:

ERROR: function json_query_array(text, unknown) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Position: 140

Basically, I think the issue is that I am using the wrong signatures (most of the time) in the methods I am trying to use.

I used this query to make sure I can at least get the keys from the dictionary:

SELECT JSON_OBJECT_KEYS(data_field::json)
FROM my_table

I was not able to use JSON_OBJECT_KEYS() without adding the ::json cast, I was getting this error:

ERROR: function json_object_keys(text) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Position: 127

But with the json cast, I am getting all the keys as intended.


Thank you for taking a look!


EDIT:
I also found this interesting article with different solution but none of the solutions worked.
Also seen this SO post which did not help.

Advertisement

Answer

Ok, after some more digging around, I found this article, which had the correct format/syntax.

This code is what I used to fetch the list from the JSON object successfully:

select data_field::json->'my_list' as the_list
from my_table 

Then, I used json_array_length() to get the number of elements:

select json_array_length(data_field::json->'my_list') as number_of_elements
from my_table 

All done! 🙂

EDIT:
I just found the reason to this whole shenanigan. In the code (which goes years back) we used this package:

jsonfield==1.0.3

And used this way:

from jsonfield import JSONField

The issue is that in the background, Postgres saves the data as a string, so it needs to be cast into a JSON. Later Django introduced its own JSONField, which stores data as you would expect, without a need to cast:

from django.contrib.postgres.fields import JSONField
User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement