复杂查询语句汇总

pgsql

查询json中的key，并且将key合并为一个字段

sql
select fp.barcode,fp.person_name,fp.gender,fp.citizen_id_number,fp.birthday,fp.permanent_residence_area_code,
  fp.permanent_residence_address,fp.current_residence_area_code,fp.current_residence_address,
  fp.data_collect_date,fp.data_collect_organization_code,fp.data_collect_person_name,
  print.print_index
  from fingerprint_personinfo fp left join (select string_agg(key,',') as print_index,max(id) as sub_id from( 
SELECT jsonb_object_keys(info_detail->'print_set') as key,id FROM fingerprint_personinfo fp where id=1155) as subquery) print on print.sub_id=fp.id

django orm 将数据表时间字段➕8个小时搜索

python
from django.db.models import Func,ExpressionWrapper

class Add8Hours(Func):
    function = 'TIMESTAMPADD'
    template = "%(function)s(HOUR, %(expressions)s, 8)"
    

print_date_str = str(row[5]).strip().replace('nan','')
if not print_date_str:
    error = '捺印日期 is null'
    chunk.loc[index, 'error'] = error
    continue
date_format = "%Y%m%d%H%M%S"

print_date = datetime.strptime(print_date_str, date_format)


person_info = PersonInfo.objects.annotate(new_timestamp=ExpressionWrapper(
            F('data_collect_date') + timedelta(hours=8),
             output_field=DateTimeField())).filter(citizen_id_number=id_card,
             original_barcode=barcode,new_timestamp__date=print_date).first()

查询某个json字段，其中某个字段为列表，按照其列表的元素进行搜索查询

sql
select fp.info_detail->'collecting_reason_list' from fingerprint_personinfo fp  order by fp.info_detail->'collecting_reason_list' desc;


SELECT * FROM my_table WHERE 'swimming' = ANY (json_column->'hobbies')


select fp.info_detail->'collecting_reason_list' from fingerprint_personinfo fp where '21' = ANY(info_detail->'collecting_reason_list');


SELECT fp.info_detail->'collecting_reason_list',is_default
FROM fingerprint_personinfo fp
WHERE info_detail -> 'collecting_reason_list' ?| array['21'];


SELECT fp.info_detail->'case_class_set',is_default
FROM fingerprint_caseinfo fp
WHERE info_detail -> 'case_class_set' ?| array['05000100','04000100'];



select fc.info_detail->'case_class_set' from fingerprint_caseinfo fc;

目录

pgsql