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)

​x
 
from django.db import modelsfrom jsonfield import JSONField​class MyTable(models.Model):  data_field = JSONField(blank=True, default=dict)​

Example of the dictionary stored in data_field:

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

 
{..., "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

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

Error:

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

 
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

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

Error:

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

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

Attempt:

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

 
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

 
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

 
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

 
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

 
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

 
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.

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

 
select data_field::json->'my_list' as the_listfrom 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

 
select json_array_length(data_field::json->'my_list') as number_of_elementsfrom 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

 
jsonfield==1.0.3​

And used this way:

from jsonfield import JSONField

 
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

 
from django.contrib.postgres.fields import JSONField​

Advertisement

Answer