postgresJsonApi.js 5.6 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161
  1. const jsonFieldExpressionParser = require('../../parsers/jsonFieldExpressionParser');
  2. const { asArray } = require('../../../utils/objectUtils');
  3. /**
  4. * @typedef {String} FieldExpression
  5. *
  6. * Field expressions allow one to refer to separate JSONB fields inside columns.
  7. *
  8. * Syntax: <column reference>[:<json field reference>]
  9. *
  10. * e.g. `Person.jsonColumnName:details.names[1]` would refer to value `'Second'`
  11. * in column `Person.jsonColumnName` which has
  12. * `{ details: { names: ['First', 'Second', 'Last'] } }` object stored in it.
  13. *
  14. * First part `<column reference>` is compatible with column references used in
  15. * knex e.g. `MyFancyTable.tributeToThBestColumnNameEver`.
  16. *
  17. * Second part describes a path to an attribute inside the referred column.
  18. * It is optional and it always starts with colon which follows directly with
  19. * first path element. e.g. `Table.jsonObjectColumnName:jsonFieldName` or
  20. * `Table.jsonArrayColumn:[321]`.
  21. *
  22. * Syntax supports `[<key or index>]` and `.<key or index>` flavors of reference
  23. * to json keys / array indexes:
  24. *
  25. * e.g. both `Table.myColumn:[1][3]` and `Table.myColumn:1.3` would access correctly
  26. * both of the following objects `[null, [null,null,null, "I was accessed"]]` and
  27. * `{ "1": { "3" : "I was accessed" } }`
  28. *
  29. * Caveats when using special characters in keys:
  30. *
  31. * 1. `objectColumn.key` This is the most common syntax, good if you are
  32. * not using dots or square brackets `[]` in your json object key name.
  33. * 2. Keys containing dots `objectColumn:[keywith.dots]` Column `{ "keywith.dots" : "I was referred" }`
  34. * 3. Keys containing square brackets `column['[]']` `{ "[]" : "This is getting ridiculous..." }`
  35. * 4. Keys containing square brackets and quotes
  36. * `objectColumn:['Double."Quote".[]']` and `objectColumn:["Sinlge.'Quote'.[]"]`
  37. * Column `{ "Double.\"Quote\".[]" : "I was referred", "Single.'Quote'.[]" : "Mee too!" }`
  38. * 99. Keys containing dots, square brackets, single quotes and double quotes in one json key is
  39. * not currently supported
  40. */
  41. function parseFieldExpression(expression, extractAsText) {
  42. let parsed = jsonFieldExpressionParser.parse(expression);
  43. let jsonRefs = parsed.access.map(it => it.ref).join(',');
  44. let extractor = extractAsText ? '#>>' : '#>';
  45. let middleQuotedColumnName = parsed.columnName.split('.').join('"."');
  46. return `"${middleQuotedColumnName}"${extractor}'{${jsonRefs}}'`;
  47. }
  48. function whereJsonbRefOnLeftJsonbValOrRefOnRight(
  49. builder,
  50. fieldExpression,
  51. operator,
  52. jsonObjectOrFieldExpression,
  53. queryPrefix
  54. ) {
  55. let queryParams = whereJsonbRefOnLeftJsonbValOrRefOnRightRawQueryParams(
  56. fieldExpression,
  57. operator,
  58. jsonObjectOrFieldExpression,
  59. queryPrefix
  60. );
  61. return builder.whereRaw.apply(builder, queryParams);
  62. }
  63. function whereJsonbRefOnLeftJsonbValOrRefOnRightRawQueryParams(
  64. fieldExpression,
  65. operator,
  66. jsonObjectOrFieldExpression,
  67. queryPrefix
  68. ) {
  69. let fieldReference = parseFieldExpression(fieldExpression);
  70. if (typeof jsonObjectOrFieldExpression === 'string') {
  71. let rightHandReference = parseFieldExpression(jsonObjectOrFieldExpression);
  72. let refRefQuery = [
  73. '(',
  74. fieldReference,
  75. ')::jsonb',
  76. operator,
  77. '(',
  78. rightHandReference,
  79. ')::jsonb'
  80. ];
  81. if (queryPrefix) {
  82. refRefQuery.unshift(queryPrefix);
  83. }
  84. return [refRefQuery.join(' ')];
  85. } else if (jsonObjectOrFieldExpression && typeof jsonObjectOrFieldExpression === 'object') {
  86. let refValQuery = ['(', fieldReference, ')::jsonb', operator, '?::jsonb'];
  87. if (queryPrefix) {
  88. refValQuery.unshift(queryPrefix);
  89. }
  90. return [refValQuery.join(' '), JSON.stringify(jsonObjectOrFieldExpression)];
  91. }
  92. throw new Error('Invalid right hand expression.');
  93. }
  94. function whereJsonFieldRightStringArrayOnLeftQuery(knex, fieldExpression, operator, keys) {
  95. let fieldReference = parseFieldExpression(fieldExpression);
  96. keys = asArray(keys);
  97. let questionMarksArray = keys.map(key => {
  98. if (typeof key !== 'string') {
  99. throw new Error('All keys to find must be strings.');
  100. }
  101. return '?';
  102. });
  103. let rawSqlTemplateString = 'array[' + questionMarksArray.join(',') + ']';
  104. let rightHandExpression = knex.raw(rawSqlTemplateString, keys);
  105. return `${fieldReference} ${operator.replace('?', '\\?')} ${rightHandExpression}`;
  106. }
  107. function whereJsonFieldQuery(knex, fieldExpression, operator, value) {
  108. let fieldReference = parseFieldExpression(fieldExpression, true);
  109. let normalizedOperator = normalizeOperator(knex, operator);
  110. // json type comparison takes json type in string format
  111. let cast;
  112. let escapedValue = knex.raw(' ?', [value]);
  113. let type = typeof value;
  114. if (type === 'number') {
  115. cast = '::NUMERIC';
  116. } else if (type === 'boolean') {
  117. cast = '::BOOLEAN';
  118. } else if (type === 'string') {
  119. cast = '::TEXT';
  120. } else if (value === null) {
  121. cast = '::TEXT';
  122. escapedValue = 'NULL';
  123. } else {
  124. throw new Error('Value must be string, number, boolean or null.');
  125. }
  126. return `(${fieldReference})${cast} ${normalizedOperator} ${escapedValue}`;
  127. }
  128. function normalizeOperator(knex, operator) {
  129. let trimmedLowerCase = operator.trim().toLowerCase();
  130. switch (trimmedLowerCase) {
  131. case 'is':
  132. case 'is not':
  133. return trimmedLowerCase;
  134. default:
  135. return knex.client.formatter().operator(operator);
  136. }
  137. }
  138. module.exports = {
  139. parseFieldExpression: parseFieldExpression,
  140. whereJsonbRefOnLeftJsonbValOrRefOnRight: whereJsonbRefOnLeftJsonbValOrRefOnRight,
  141. whereJsonbRefOnLeftJsonbValOrRefOnRightRawQueryParams: whereJsonbRefOnLeftJsonbValOrRefOnRightRawQueryParams,
  142. whereJsonFieldRightStringArrayOnLeftQuery: whereJsonFieldRightStringArrayOnLeftQuery,
  143. whereJsonFieldQuery: whereJsonFieldQuery
  144. };