Build JSON in Postgres-Select

PHOTO

Fri Mar 03 2023 10:57:14 GMT+0000 (Coordinated Universal Time)

Saved by @ClemensBerteld #postgres #sql #json

SELECT
    planname,
    '{"c":"baz"}'::JSON,
    json_build_object(planname,1,'bar',2)
FROM bplaene
LIMIT 10
;

-- Complex nested JSON with arrays
SELECT json_build_object(
               'trans_id', t.trans_id,
               'user_data', json_build_object(
                       'user_id', t.user_id,
                       'user_username', t.user_username,
                       'user_full_name', t.user_full_name,
                       'user_street', t.user_street,
                       'user_postal_code', t.user_postal_code,
                       'user_additional_info', t.user_additional_info,
                       'user_country', t.user_country,
                       'user_vat_number', t.user_vat_number),
               'order_data', json_build_object(
                       'order_date', t.order_date,
                       'order_sum', t.order_sum,
                       'order_vat', t.order_vat,
                       'order_invoice_nr', t.order_invoice_nr
                   ),
               'locations',
               (SELECT json_agg(row_to_json(locations))
                FROM (SELECT l.address,
                             l.project_title,
                             (SELECT json_agg(row_to_json(f))
                              FROM (SELECT layername,
                                           data
                                    FROM sales.features) f) features

                      FROM sales.locations l) locations)
           ) transaction
FROM sales.transactions t

Save snippets that work from anywhere online with our extensions

Available in the Chrome Web Store

Get Firefox Add-on

Get VS Code extension

Comments

More like this

SQL

@ClemensBerteld

Build JSON in Postgres-Select SELECT column name and datatype in Postgres Query Killer in PostgreSQL

[
   {
      "mostrar":"in,out",
      "label":"CURSO DE FORMAÇÃO EM TERAPEUTA AYURVEDA",
      "campo":"cursotitulo",
      "tipo":"titulo6",
      "colunas_in":"12",
      "colunas_out":"12"
   },
   {
      "mostrar":"in,out",
      "label":"Nome Completo",
      "campo":"nomecompleto",
      "tipo":"text",
      "colunas_in":"12",
      "colunas_out":"4",
      "requerido":"1"
   },
   {
      "mostrar":"in,out",
      "label":"Data de nascimento",
      "campo":"data_nasc",
      "tipo":"data",
      "colunas_in":"6",
      "colunas_out":"4",
      "requerido":"1"
   },
   {
      "mostrar":"in,out",
      "label":"CPF",
      "campo":"nrocpf",
      "tipo":"cpf",
      "colunas_in":"6",
      "colunas_out":"4",
      "requerido":"1"
   },
   {
      "mostrar":"in,out",
      "label":"Estado Civil",
      "campo":"estado_civil",
      "tipo":"select_valor",
      "colunas_in":"6",
      "colunas_out":"4",
      "opcoes":[
         "Casado",
         "Solteiro",
         "Separado",
         "Viuvo"
      ],
      "requerido":"1"
   },
   {
      "mostrar":"in,out",
      "label":"Profissão",
      "campo":"profi",
      "tipo":"text",
      "colunas_in":"6",
      "colunas_out":"4",
      "requerido":"1"
   },
   {
      "mostrar":"in,out",
      "label":"Email",
      "campo":"email",
      "tipo":"text",
      "colunas_in":"12",
      "colunas_out":"4",
      "requerido":"1"
   },
   {
      "mostrar":"in,out",
      "label":"Telefone Celular",
      "campo":"telcelular",
      "tipo":"text",
      "placeholder":"(xx) x xxxx-xxxx",
      "colunas_in":"6",
      "colunas_out":"6",
      "requerido":"1"
   },
   {
      "mostrar":"in,out",
      "label":"Telefone Residencial",
      "campo":"telresidencial",
      "tipo":"text",
      "placeholder":"(xx) x xxxx-xxxx",
      "colunas_in":"6",
      "colunas_out":"6"
   },
   {
      "mostrar":"in,out",
      "label":"Endereço",
      "campo":"logradouro",
      "tipo":"text",
      "colunas_in":"6",
      "colunas_out":"4",
      "requerido":"1"
   },
   {
      "mostrar":"in,out",
      "label":"Cidade",
      "campo":"cid",
      "tipo":"text",
      "colunas_in":"6",
      "colunas_out":"4",
      "requerido":"1"
   },
   {
      "mostrar":"in,out",
      "label":"CEP",
      "campo":"cep_user",
      "tipo":"text",
      "colunas_in":"6",
      "colunas_out":"4",
      "requerido":"1"
   },
   {
      "mostrar":"in,out",
      "label":"PAGAMENTO",
      "campo":"pagamento",
      "tipo":"titulo6",
      "colunas_in":"12",
      "colunas_out":"12"
   },
   {
      "mostrar":"in,out",
      "label":"Forma de Pagamento",
      "campo":"forma_pagamento",
      "tipo":"select_valor",
      "colunas_in":"6",
      "colunas_out":"6",
      "requerido":"1",
      "opcoes":[
         "À vista",
         "À prazo",
         "Boleto",
         "Cheque"
      ],
      "conditions":[
         {
            "when":"equal",
            "value":"À prazo",
            "action":{
               "show":[
                  "parcelas"
               ],
               "required":[
                  "parcelas"
               ]
            }
         }
      ]
   },
   {
      "mostrar":"in,out",
      "label":"Em quantas parcelas?",
      "campo":"parcelas",
      "tipo":"number",
      "colunas_in":"12",
      "colunas_out":"12",
      "classe":"hide"
   },
   {
      "mostrar":"in,out",
      "label":"Escola",
      "campo":"escola",
      "tipo":"select_valor",
      "colunas_in":"12",
      "colunas_out":"12",
      "requerido":"1",
      "opcoes":[
         "Semente da Paz",
         "Leveza do Ser",
         "Beija Flor",
         "La vie",
         "Karina Gomes"
      ],
      "aceita_recategorizar":"1"
   },
   {
      "mostrar":"in,out",
      "label":"TERMO DE AUTORIZAÇÃO",
      "campo":"termo",
      "tipo":"titulo6",
      "colunas_in":"12",
      "colunas_out":"12"
   },
   {
      "mostrar":"in,out",
      "campo":"autori",
      "tipo":"texto",
      "valor_padrao":"Eu, autorizo a gravar (minha imagem em vídeo ou fotografia) e veicular minha imagem e depoimentos em qualquer meios de comunicação para fins didáticos, de pesquisa e divulgação de conhecimento científico sem quaisquer ônus e restrições. Fica ainda autorizada, de livre e espontânea vontade, para os mesmos fins, a cessão de direitos da veiculação, não recebendo para tanto qualquer tipo de remuneração. ",
      "colunas_in":"12",
      "colunas_out":"12"
   },
   {
      "mostrar":"in,out",
      "label":"Eu concordo e autorizo",
      "campo":"concordancia",
      "tipo":"checkbox",
      "colunas_in":"12",
      "colunas_out":"12",
      "requerido":"1"
   }
]

javascript - Import sql file in node.js and execute against PostgreSQL - Stack Overflow

function processSQLFile(fileName) {

  // Extract SQL queries from files. Assumes no ';' in the fileNames
  var queries = fs.readFileSync(fileName).toString()
    .replace(/(\r\n|\n|\r)/gm," ") // remove newlines
    .replace(/\s+/g, ' ') // excess white space
    .split(";") // split into all statements
    .map(Function.prototype.call, String.prototype.trim)
    .filter(function(el) {return el.length != 0}); // remove any empty ones

  // Execute each SQL query sequentially
  queries.forEach(function(query) {
    batch.push(function(done) {
      if (query.indexOf("COPY") === 0) { // COPY - needs special treatment
        var regexp = /COPY\ (.*)\ FROM\ (.*)\ DELIMITERS/gmi;
        var matches = regexp.exec(query);
        var table = matches[1];
        var fileName = matches[2];
        var copyString = "COPY " + table + " FROM STDIN DELIMITERS ',' CSV HEADER";
        var stream = client.copyFrom(copyString);
        stream.on('close', function () {
          done();
        });
        var csvFile = __dirname + '/' + fileName;
        var str = fs.readFileSync(csvFile);
        stream.write(str);
        stream.end();
      } else { // Other queries don't need special treatment
        client.query(query, function(result) {
          done();
        });
      }
    });
  });
}

#java #howto #json

How to parse JSON in Java

import org.json.*;


JSONObject obj = new JSONObject(" .... ");
String pageName = obj.getJSONObject("pageInfo").getString("pageName");

JSONArray arr = obj.getJSONArray("posts");
for (int i = 0; i < arr.length(); i++)
{
    String post_id = arr.getJSONObject(i).getString("post_id");
    ......
}

#javascript #json #jsfunctions

JSON.parse() - convert string into a JavaScript object

var contacts = '{ "people" : [' +
'{ "firstName":"Joe" , "lastName":"Smith" },' +
'{ "firstName":"Tom" , "lastName":"Hardy" },' +
'{ "firstName":"Ben" , "lastName":"Stiller" } ]}';

var newObject = JSON.parse(contacts);

#javascript #json #objects #jsvariables

Creating a JSON Object in Javascript

var eventsVariable = '"{events":[' +
    '{"location": "New York", "date": "May 1", "public": "true"},' +
    '{"location": "London", "date": "Apr 24", "public": "false"},' +
    '{"location": "San Frans", "date": "Nov 30", "public": "false"}]}';

JSON String Escape / Unescape

Escapes or unescapes a JSON string removing traces of offending characters that could prevent parsing.

The following characters are reserved in JSON and must be properly escaped to be used in strings:

Backspace is replaced with \b
Form feed is replaced with \f
Newline is replaced with \n
Carriage return is replaced with \r
Tab is replaced with \t
Double quote is replaced with \"
Backslash is replaced with \\

Manifest file format - Chrome Developers

{
  // Required
  "manifest_version": 3,
  "name": "My Extension",
  "version": "versionString",

  // Recommended
  "action": {...},
  "default_locale": "en",
  "description": "A plain text description",
  "icons": {...},

  // Optional
  "author": ...,
  "automation": ...,
  "background": {
    // Required
    "service_worker": "background.js",
    // Optional
    "type": ...
  },
  "chrome_settings_overrides": {...},
  "chrome_url_overrides": {...},
  "commands": {...},
  "content_capabilities": ...,
  "content_scripts": [{...}],
  "content_security_policy": {...},
  "converted_from_user_script": ...,
  "cross_origin_embedder_policy": {"value": "require-corp"},
  "cross_origin_opener_policy": {"value": "same-origin"},
  "current_locale": ...,
  "declarative_net_request": ...,
  "devtools_page": "devtools.html",
  "differential_fingerprint": ...,
  "event_rules": [{...}],
  "externally_connectable": {
    "matches": ["*://*.example.com/*"]
  },
  "file_browser_handlers": [...],
  "file_system_provider_capabilities": {
    "configurable": true,
    "multiple_mounts": true,
    "source": "network"
  },
  "homepage_url": "https://path/to/homepage",
  "host_permissions": [...],
  "import": [{"id": "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"}],
  "incognito": "spanning, split, or not_allowed",
  "input_components": ...,
  "key": "publicKey",
  "minimum_chrome_version": "versionString",
  "nacl_modules": [...],
  "natively_connectable": ...,
  "oauth2": ...,
  "offline_enabled": true,
  "omnibox": {
    "keyword": "aString"
  },
  "optional_host_permissions": ["..."],
  "optional_permissions": ["tabs"],
  "options_page": "options.html",
  "options_ui": {
    "page": "options.html"
  },
  "permissions": ["tabs"],
  "platforms": ...,
  "replacement_web_app": ...,
  "requirements": {...},
  "sandbox": [...],
  "short_name": "Short Name",
  "storage": {
    "managed_schema": "schema.json"
  },
  "system_indicator": ...,
  "tts_engine": {...},
  "update_url": "https://path/to/updateInfo.xml",
  "version_name": "aString",
  "web_accessible_resources": [...]
}

mysql FIND_IN_SET equivalent to postgresql

select * 
from folder f
  join uploads u ON u.id = f.folderId 
where '8' = ANY (string_to_array(some_column,','))

sql.How to get Quarter’s Start and End Date for a given date in Sql Server

DECLARE @AnyDate DATETIME
SET @AnyDate = GETDATE()

SELECT @AnyDate AS 'Input Date',
  DATEADD(q, DATEDIFF(q, 0, @AnyDate), 0) 
                        AS 'Quarter Start Date',       
  DATEADD(d, -1, DATEADD(q, DATEDIFF(q, 0, @AnyDate) + 1, 0)) 
                        AS 'Quarter End Date'

#javascript #json #jsfunctions

JSON.stringify() - convert objects into JSON

var profile = {
  name: 'John',
  age: 25,
  isAdmin: false,
  courses: ['html', 'css', 'js']
};

var person = JSON.stringify(profile);

package.json PORTFOLIO

{
  "name": "portfolio",
  "version": "1.0.0",
  "description": "Serhii Yakymenko Site potrfolio.",
  "main": "gulpfile.js",
  "dependencies": {
    "browser-sync": "^2.26.7"
  },
  "devDependencies": {
    "gulp": "^4.0.2",
    "gulp-sass": "^4.1.0"
  },
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "repository": {
    "type": "git",
    "url": "git+https://github.com/serhii-edit/portfolio.git"
  },
  "author": "Serhii Yakymenko",
  "license": "ISC",
  "bugs": {
    "url": "https://github.com/serhii-edit/portfolio/issues"
  },
  "homepage": "https://github.com/serhii-edit/portfolio#readme"
}

Refining Access to Branches in AWS CodeCommit | AWS DevOps Blog

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Deny",
            "Action": [
                "codecommit:GitPush",
                "codecommit:DeleteBranch",
                "codecommit:PutFile",
                "codecommit:MergePullRequestByFastForward"
            ],
            "Resource": "arn:aws:codecommit:us-east-2:80398EXAMPLE:MyDemoRepo",
            "Condition": {
                "StringEqualsIfExists": {
                    "codecommit:References": [
                        "refs/heads/master"   
                    ]
                },
                "Null": {
                    "codecommit:References": false
                }
            }
        }
    ]
}

OnePipe v1 - Documentation

{
  "status": "Successful",
  "message": "The transaction was processed successfully",
  "data": {
    "provider_response_code": "00",
    "charge_status": "Processing | WaitingForOTP | ProcessingOTP | Successful | Failed | OfflineValidated | OfflineNotified",
    "provider": "Quickteller",
    "errors": null,
    "error": null,
    "charge_token": "Kz5Dev7BenV9HmLNB"
  }
}

Slide References

"pages": [
	{
		"id": "references",
		"content": "<div style='width: 1024px; height: 580px; background-color: #fafafa;'><div style='padding: 20px;'><b>Sources:</b><br/><br/>Active LCDs, Noridian Healthcare Solutions.<br/>https://med.noridianmedicare.com/web/jeb/policies<br/><br/>Chapter 13 – Local  Coverage Determinations, Medicare Program Integrity Manual, Centers for Medicare & Medicaid Services.<br/>https://www.cms.gov/Regulations-and-Guidance/Regulations-and-Guidance.html<br/><br/>Indexes, Centers for Medicare & Medicaid Services.<br/>https://www.cms.gov/medicare-coverage-database/indexes/national-and-local-indexes.aspx<br/><br/>Local Coverage Determinations Create Inconsistency in Medicare Coverage, Office of Inspector General, Department of Health and Human Services.<br/>http://oig.hhs.gov<br/><br/>Medicare Coverage Determination Process, Centers for Medicare & Medicaid Services.<br/>https://www.cms.gov/Medicare/Medicare.html<br/><br/>Regional Map, CMS Regional Offices, Centers for Medicare & Medicaid Services.<br/>https://www.cms.gov/About-CMS/About-CMS.html<br/><br/>United States Department of Health and Human Services.<br/>www.hhs.gov</div></div>"
	}
],

#json #nodejs #debug

VSCode node debugger launch file

{
  // Use IntelliSense to learn about possible attributes.
  // Hover to view descriptions of existing attributes.
  // For more information, visit: https://go.microsoft.com/fwlink/?linkid=830387
  "version": "0.2.0",
  "configurations": [
    {
      "type": "node",
      "request": "attach",
      // "processId": "${command:PickProcess}",
      "protocol": "inspector",
      "restart": true,
      "name": "Launch Programa",
      "skipFiles": ["<node_internals>/**"]
    }
  ]
}

Browse more snippets >>