Обсуждение: [SQL] autogenerated json path
Hi,
--
im first time using json data type and i want to autogenerate it, because im creating crawler which uses postgresql.
This is static example for one site where path should be automatically generate
SELECT
file_name
,json_data#>>'{1,children,1,children,5,children,3,children,0,children,22,children,0,children,1,children,0,content}'
from src_data
where file_name ~ 'monitor.hr';
in case that i want to add into table another sites and their paths, i would like to write query which automaticaly changes paths.
SELECT
file_name
,json_data#>>'{''||r.json_path ||''}'
,r.json_path
from src_data d,rules r
where file_name like r.file_name_prefix||'%';
is there any way to write this without writing functions which would slow process?
---------------------------------------
Viktor Bojović
Viktor Bojović
forget previous email, it is solved using function:
create or replace function json_path2txt(data json, jpath varchar) RETURNS text AS $$
DECLARE
_r record;
_sql varchar;
txt varchar;
BEGIN
_sql:='select data#>>''{'||jpath||'}'' as txt';
-- raise notice '%',_sql ;
EXECUTE(_sql) into txt;
return txt;
END
$$ LANGUAGE plpgsql
IMMUTABLE
RETURNS NULL ON NULL INPUT;
On Thu, Aug 31, 2017 at 11:42 PM, Viktor Bojović <viktor.bojovic@gmail.com> wrote:
Hi,im first time using json data type and i want to autogenerate it, because im creating crawler which uses postgresql.This is static example for one site where path should be automatically generateSELECTfile_name,json_data#>>'{1,children,1,children,5,children,3, children,0,children,22, children,0,children,1, children,0,content}' from src_datawhere file_name ~ 'monitor.hr';in case that i want to add into table another sites and their paths, i would like to write query which automaticaly changes paths.SELECTfile_name,json_data#>>'{''||r.json_path ||''}',r.json_pathfrom src_data d,rules rwhere file_name like r.file_name_prefix||'%';is there any way to write this without writing functions which would slow process?-----------------------------------------
Viktor Bojović
---------------------------------------
Viktor Bojović
Viktor Bojović