Funciones JSON#
Ultima modificación: Mar 6, 2024 | YouTube
Preparacion
[1]:
import sqlite3
from IPython.core.magic import register_cell_magic, register_line_magic
conn = sqlite3.connect(":memory:")
cur = conn.cursor()
@register_line_magic
def sql(line):
return cur.execute(line).fetchall()
Funciones
[2]:
##
## json(X)
## verifica si su argumento es un string json valido y retorna
## una version con los espacios en blanco removidos.
##
%sql SELECT json(' { "this" : "is", "a": [ "test" ] } ')
[2]:
[('{"this":"is","a":["test"]}',)]
[3]:
##
## json_array(X1, ...)
## recibe 0 o mas argumentos y retorna un string json
##
%sql SELECT json_array(1,2,'3',4)
[3]:
[('[1,2,"3",4]',)]
[4]:
%sql SELECT json_array('[1,2]')
[4]:
[('["[1,2]"]',)]
[5]:
%sql SELECT json_array(json_array(1,2))
[5]:
[('[[1,2]]',)]
[6]:
%sql SELECT json_array(1,null,'3','[4,5]','{"six":7.7}')
[6]:
[('[1,null,"3","[4,5]","{\\"six\\":7.7}"]',)]
[7]:
%sql SELECT json_array(1,null,'3',json('[4,5]'),json('{"six":7.7}'))
[7]:
[('[1,null,"3",[4,5],{"six":7.7}]',)]
[8]:
##
## json_extract(X, P1, P2, ...)
##
%sql SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$')
[8]:
[('{"a":2,"c":[4,5,{"f":7}]}',)]
[9]:
%sql SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c')
[9]:
[('[4,5,{"f":7}]',)]
[10]:
%sql SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2]')
[10]:
[('{"f":7}',)]
[11]:
%sql SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2].f')
[11]:
[(7,)]
[12]:
%sql SELECT json_extract('{"a":2,"c":[4,5],"f":7}','$.c','$.a')
[12]:
[('[[4,5],2]',)]
[13]:
%sql SELECT json_extract('{"a":2,"c":[4,5],"f":7}','$.c[#-1]')
[13]:
[(5,)]
[14]:
%sql SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x')
[14]:
[(None,)]
[15]:
%sql SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x', '$.a')
[15]:
[('[null,2]',)]
[16]:
%sql SELECT json_extract('{"a":"xyz"}', '$.a')
[16]:
[('xyz',)]
[17]:
%sql SELECT json_extract('{"a":null}', '$.a')
[17]:
[(None,)]
[18]:
##
## Operadores -> y ->>
## Permiten extraer subcomponentes del json.
##
%sql SELECT '{"a":2,"c":[4,5,{"f":7}]}' -> '$'
[18]:
[('{"a":2,"c":[4,5,{"f":7}]}',)]
[19]:
%sql SELECT '{"a":2,"c":[4,5,{"f":7}]}' -> '$.c'
[19]:
[('[4,5,{"f":7}]',)]
[20]:
%sql SELECT '{"a":2,"c":[4,5,{"f":7}]}' -> 'c'
[20]:
[('[4,5,{"f":7}]',)]
[21]:
%sql SELECT '{"a":2,"c":[4,5,{"f":7}]}' -> '$.c[2]'
[21]:
[('{"f":7}',)]
[22]:
%sql SELECT '{"a":2,"c":[4,5,{"f":7}]}' -> '$.c[2].f'
[22]:
[('7',)]
[23]:
%sql SELECT '{"a":2,"c":[4,5],"f":7}' -> '$.c[#-1]'
[23]:
[('5',)]
[24]:
%sql SELECT '{"a":2,"c":[4,5,{"f":7}]}' -> '$.x'
[24]:
[(None,)]
[25]:
%sql SELECT '[11,22,33,44]' -> 3
[25]:
[('44',)]
[26]:
%sql SELECT '[11,22,33,44]' ->> 3
[26]:
[(44,)]
[27]:
%sql SELECT '{"a":"xyz"}' -> '$.a'
[27]:
[('"xyz"',)]
[28]:
%sql SELECT '{"a":"xyz"}' ->> '$.a'
[28]:
[('xyz',)]
[29]:
%sql SELECT '{"a":null}' -> '$.a'
[29]:
[('null',)]
[30]:
%sql SELECT '{"a":null}' ->> '$.a'
[30]:
[(None,)]
[31]:
##
## json_insert()
##
%sql SELECT json_insert('[1,2,3,4]','$[#]',99)
[31]:
[('[1,2,3,4,99]',)]
[32]:
%sql SELECT json_insert('[1,[2,3],4]','$[1][#]',99)
[32]:
[('[1,[2,3,99],4]',)]
[33]:
%sql SELECT json_insert('{"a":2,"c":4}', '$.a', 99)
[33]:
[('{"a":2,"c":4}',)]
[34]:
%sql SELECT json_insert('{"a":2,"c":4}', '$.e', 99)
[34]:
[('{"a":2,"c":4,"e":99}',)]
[35]:
##
## json_replace()
##
%sql SELECT json_replace('{"a":2,"c":4}', '$.a', 99)
[35]:
[('{"a":99,"c":4}',)]
[36]:
%sql SELECT json_replace('{"a":2,"c":4}', '$.e', 99)
[36]:
[('{"a":2,"c":4}',)]
[37]:
%sql SELECT json_set('{"a":2,"c":4}', '$.a', 99)
[37]:
[('{"a":99,"c":4}',)]
[38]:
%sql SELECT json_set('{"a":2,"c":4}', '$.e', 99)
[38]:
[('{"a":2,"c":4,"e":99}',)]
[39]:
%sql SELECT json_set('{"a":2,"c":4}', '$.c', '[97,96]')
[39]:
[('{"a":2,"c":"[97,96]"}',)]
[40]:
%sql SELECT json_set('{"a":2,"c":4}', '$.c', json('[97,96]'))
[40]:
[('{"a":2,"c":[97,96]}',)]
[41]:
%sql SELECT json_set('{"a":2,"c":4}', '$.c', json_array(97,96))
[41]:
[('{"a":2,"c":[97,96]}',)]
[42]:
##
## json_object()
## acepta 0 o un número par de argumentos y retorna una cadena json valida
##
%sql SELECT json_object('a',2,'c',4)
[42]:
[('{"a":2,"c":4}',)]
[43]:
%sql SELECT json_object('a',2,'c','{e:5}')
[43]:
[('{"a":2,"c":"{e:5}"}',)]
[44]:
%sql SELECT json_object('a',2,'c',json_object('e',5))
[44]:
[('{"a":2,"c":{"e":5}}',)]
[45]:
##
## json_remove(X, P, ...)
##
%sql SELECT json_remove('[0,1,2,3,4]','$[2]')
[45]:
[('[0,1,3,4]',)]
[46]:
%sql SELECT json_remove('[0,1,2,3,4]','$[2]','$[0]')
[46]:
[('[1,3,4]',)]
[47]:
%sql SELECT json_remove('[0,1,2,3,4]','$[0]','$[2]')
[47]:
[('[1,2,4]',)]
[48]:
%sql SELECT json_remove('[0,1,2,3,4]','$[#-1]','$[0]')
[48]:
[('[1,2,3]',)]
[49]:
%sql SELECT json_remove('{"x":25,"y":42}')
[49]:
[('{"x":25,"y":42}',)]
[50]:
%sql SELECT json_remove('{"x":25,"y":42}','$.z')
[50]:
[('{"x":25,"y":42}',)]
[51]:
%sql SELECT json_remove('{"x":25,"y":42}','$.y')
[51]:
[('{"x":25}',)]
[52]:
%sql SELECT json_remove('{"x":25,"y":42}','$')
[52]:
[(None,)]
[53]:
##
## json_valid(X)
##
%sql SELECT json_valid('{"x":35}')
[53]:
[(1,)]
[54]:
%sql SELECT json_valid('{"x":35')
[54]:
[(0,)]